SQL Server 一些查询技巧
时间：2018-12-06 10:20:01
手机看文章
扫描二维码
随时随地手机看文章
[导读]年内按月累计（如：2月累计为前两个月的，3月累计为前三个月的)CREATE TABLE T (tDate DATETIME,tValue INT)
INSERT INTO dbo.T
SELECT
年内按月累计（如：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个月内产品逐月库存
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 numberb.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
*/
下载文档
