求一个递归汇总的SQL语句

weisaisz 2014-01-15 02:50:07

原数据

年月产品未出库数量
2013 11 A 100
2013 12 A 150
2014 1 A 300
2013 10 B 1000
2013 11 B 1500
2013 12 B 3001

要求得出下面结果:

年月产品当月为止剩余未出库数量
2013 11 A 100
2013 12 A 250
2013 1 A 550
2013 10 B 1000
2013 11 B 2500
2013 12 B 5501

谢谢

...全文

210 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

小孩快跑 2014-01-17

打赏
举报

唉，不知何时我的技术才有这么牛呢，看来有空要多来学习一下啊！

Joylwu 2014-01-17

打赏
举报

“Author :DBA_Huangzj(發糞塗牆)” 牛叉，仅仅7分钟就问题解决，问题是很简单，一个CTE递归搞定，但你这建表，写代码也太神速了吧！

發糞塗牆 2014-01-15

打赏
举报


----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-15 14:52:01
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([年] int,[月] int,[产品] varchar(1),[未出库数量] int)
insert [huang]
select 2013,11,'A',100 union all
select 2013,12,'A',150 union all
select 2014,1,'A',300 union all
select 2013,10,'B',1000 union all
select 2013,11,'B',1500 union all
select 2013,12,'B',3001
--------------开始查询--------------------------
;WITH cte AS (
select *,ROW_NUMBER()OVER(PARTITION BY [产品] ORDER BY [年],[月]) id
 from [huang] )

 SELECT [年],[月],[产品],ISNULL((SELECT SUM([未出库数量]  ) FROM cte b WHERE a.id>b.id AND a.[产品]=b.[产品]),0)[未出库数量]
  FROM cte a
 ORDER BY [产品]
----------------结果----------------------------
/*
年           月           产品   未出库数量
----------- ----------- ---- -----------
2013        11          A    0
2013        12          A    100
2014        1           A    250
2013        10          B    0
2013        11          B    1000
2013        12          B    2500


*/

霜寒月冷 2014-01-15

打赏
举报

原数据年月产品未出库数量 2013 11 A 100 2013 12 A 150 2014 1 A 300 2013 10 B 1000 2013 11 B 1500 2013 12 B 3001 这个？

發糞塗牆 2014-01-15

打赏
举报

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-15 14:52:01
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([年] int,[月] int,[产品] varchar(1),[未出库数量] int)
insert [huang]
select 2013,11,'A',100 union all
select 2013,12,'A',150 union all
select 2014,1,'A',300 union all
select 2013,10,'B',1000 union all
select 2013,11,'B',1500 union all
select 2013,12,'B',3001
--------------开始查询--------------------------
;WITH cte AS (
select *,ROW_NUMBER()OVER(PARTITION BY [产品] ORDER BY [年],[月])id
 from [huang] ),
 cte1 AS (
 SELECT *
 FROM cte 
 WHERE id=1
 UNION ALL 
 SELECT a.[年],a.[月],a.[产品],a.[未出库数量]+b.[未出库数量] AS [未出库数量],a.id
 FROM cte a INNER JOIN cte1 b ON a.id-1=b.id AND a.[产品]=b.[产品]
 )
 SELECT [年],[月],[产品],[未出库数量]
  FROM cte1
 ORDER BY [产品]
----------------结果----------------------------
/* 
年           月           产品   未出库数量
----------- ----------- ---- -----------
2013        11          A    100
2013        12          A    250
2014        1           A    550
2013        10          B    1000
2013        11          B    2500
2013        12          B    5501

*/

weisaisz 2014-01-15