SQL Server 一些查詢技巧

年內(nèi)按月累計（如：2月累計為前兩個月的，3月累計為前三個月的)

CREATE?TABLE?T?(tDate?DATETIME,tValue?INT)
INSERT?INTO?dbo.T
	SELECT?'2017-01-08',10?UNION
	SELECT?'2017-01-25',20?UNION
	SELECT?'2017-02-11',30?UNION
	SELECT?'2017-02-28',40?UNION
	SELECT?'2017-03-17',50?UNION
	SELECT?'2017-04-03',60?UNION
	SELECT?'2017-04-20',70?UNION
	SELECT?'2017-05-07',80?UNION
	SELECT?'2017-05-24',90
SELECT?*?FROM?dbo.T;

SELECT?MONTH(tDate)?AS?月份,
(SELECT?SUM(tValue)?FROM?dbo.T?T1??WHERE?MONTH(T1.tDate)<=MONTH(T.tDate))?累計?
FROM?dbo.T?
GROUP?BY?MONTH(tDate)

tDate???????????????????tValue
-----------------------?-----------
2017-01-08?00:00:00.000?10
2017-01-25?00:00:00.000?20
2017-02-11?00:00:00.000?30
2017-02-28?00:00:00.000?40
2017-03-17?00:00:00.000?50
2017-04-03?00:00:00.000?60
2017-04-20?00:00:00.000?70
2017-05-07?00:00:00.000?80
2017-05-24?00:00:00.000?90


(9?行受影響)


月份??????????累計
-----------?-----------
1???????????30
2???????????100
3???????????150
4???????????280
5???????????450


(5?行受影響)

求x個月內(nèi)產(chǎn)品逐月庫存

if?object_id('[huang]')?is?not?null?drop?table?[huang]
go?
create?table?[huang]([年]?int,[月]?int,[產(chǎn)品]?varchar(1),[未出庫數(shù)量]?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.產(chǎn)品
?FROM?master..spt_values?cross?JOIN?(SELECT?DISTINCT?產(chǎn)品?FROM?[huang])?b
?WHERE?[type]='p'?AND?number>0?AND?numberb.id?AND?a.[產(chǎn)品]=b.[產(chǎn)品]),0)[未出庫數(shù)量]
??FROM?cte?a?
?ORDER?BY?[產(chǎn)品],[年],a.月
?
?/*
?年???????????月???????????產(chǎn)品???未出庫數(shù)量
-----------?-----------?----?-----------
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
?*/