原始表
id dt amount
1 2013-12-01 1
2 2013-12-01 2
3 2014-01-01 3
4 2014-01-01 4
5 2014-02-02 5
6 2014-02-03 6
7 2014-03-04 7
8 2014-03-05 8
9 2014-04-06 9
10 2014-04-07 10
11 2014-05-08 11
12 2014-05-09 12
13 2014-06-10 13
14 2014-06-11 14
15 2014-07-12 15
16 2014-07-13 16
17 2014-08-14 17
18 2014-08-15 18
19 2014-09-16 19
20 2014-09-17 20
21 2014-10-18 21
22 2014-10-19 22
23 2014-11-20 23
24 2014-11-21 24
25 2014-12-22 25
26 2014-12-23 26
27 2015-01-24 27
28 2015-01-25 28
要得到的结果
年 月 上月数 本月数
2013 12 xx 3
2014 1 3 7
2014 2 10 11
2014 3 21 15
2014 4 36 19
2014 5 55 23
2014 6 78 27
2014 7 105 31
2014 8 136 35
2014 9 171 39
2014 10 210 43
2014 11 253 47
2014 12 300 51
2015 1 351 55
上月数为小于当于1号以前所有金额的累加 ,比如现在 是9月.则上月数为8月31日之前的所有月份累加
------解决思路----------------------
WITH test(id,dt,amount) AS (
SELECT 1,'2013-12-01',1 UNION ALL
SELECT 2,'2013-12-01',2 UNION ALL
SELECT 3,'2014-01-01',3 UNION ALL
SELECT 4,'2014-01-01',4 UNION ALL
SELECT 5,'2014-02-02',5 UNION ALL
SELECT 6,'2014-02-03',6 UNION ALL
SELECT 7,'2014-03-04',7 UNION ALL
SELECT 8,'2014-03-05',8 UNION ALL
SELECT 9,'2014-04-06',9 UNION ALL
SELECT 10,'2014-04-07',10 UNION ALL
SELECT 11,'2014-05-08',11 UNION ALL
SELECT 12,'2014-05-09',12 UNION ALL
SELECT 13,'2014-06-10',13 UNION ALL
SELECT 14,'2014-06-11',14 UNION ALL
SELECT 15,'2014-07-12',15 UNION ALL
SELECT 16,'2014-07-13',16 UNION ALL
SELECT 17,'2014-08-14',17 UNION ALL
SELECT 18,'2014-08-15',18 UNION ALL
SELECT 19,'2014-09-16',19 UNION ALL
SELECT 20,'2014-09-17',20 UNION ALL
SELECT 21,'2014-10-18',21 UNION ALL
SELECT 22,'2014-10-19',22 UNION ALL
SELECT 23,'2014-11-20',23 UNION ALL
SELECT 24,'2014-11-21',24 UNION ALL
SELECT 25,'2014-12-22',25 UNION ALL
SELECT 26,'2014-12-23',26 UNION ALL
SELECT 27,'2015-01-24',27 UNION ALL
SELECT 28,'2015-01-25',28
),
d AS (
SELECT CONVERT(datetime,CONVERT(varchar(7),dt,120)+'-01',120) ym,
SUM(amount) amount
FROM test
GROUP BY CONVERT(datetime,CONVERT(varchar(7),dt,120)+'-01',120)
),
s AS (
SELECT d1.ym,
SUM(d0.amount) 上月数,
d1.amount 本月数
FROM d d1
LEFT JOIN d d0 ON d0.ym < d1.ym
GROUP BY d1.ym, d1.amount
)
SELECT YEAR(ym) 年,
MONTH(ym) 月,
上月数,
本月数
FROM s
年 月 上月数 本月数
----------- ----------- ----------- -----------
2013 12 NULL 3
2014 1 3 7
2014 2 10 11
2014 3 21 15
2014 4 36 19