34,593
社区成员
发帖
与我相关
我的任务
分享
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 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 d AS
(
SELECT CONVERT(VARCHAR(10),DATEADD(mm,number,'2013-11-01'),120)[date],b.产品
FROM master..spt_values cross JOIN (SELECT DISTINCT 产品 FROM [huang]) b
WHERE [type]='p' AND number>0 AND number<7 --这个7可以控制月份
),cte AS (
select ISNULL([年],YEAR(d.[date])) [年],ISNULL([月],month(d.[date])) [月],ISNULL(a.[产品],d.产品) 产品,ISNULL([未出库数量],0)[未出库数量],ROW_NUMBER()OVER(PARTITION BY ISNULL(a.[产品],d.产品) ORDER BY ISNULL([年],YEAR(d.[date])),ISNULL([月],month(d.[date]))) id
from [huang] a full JOIN d ON a.[年]=YEAR(d.[date]) AND a.[月]=MONTH(d.[date]) AND a.[产品]=d.产品
)
SELECT [年],[月],[产品],ISNULL((SELECT SUM([未出库数量] ) FROM cte b WHERE a.id>b.id AND a.[产品]=b.[产品]),0)[未出库数量]
FROM cte a
ORDER BY [产品],[年],a.月
/*
年 月 产品 未出库数量
----------- ----------- ---- -----------
2013 11 A 0
2013 12 A 100
2014 1 A 100
2014 2 A 400
2014 3 A 400
2014 4 A 400
2014 5 A 400
2013 10 B 0
2013 11 B 1000
2013 12 B 2500
2014 1 B 5501
2014 2 B 5501
2014 3 B 5501
2014 4 B 5501
2014 5 B 5501
*/