T-SQL 运用自连接逐行统计sum值

主要是利用聚合函数通过自连接去实现分组逐行计算某列的sum值

这是统计前

统计后结果为

实现代码如下：

 1 create table sales
 2 ( 
 3   productname nvarchar(50) 
 4  ,dateofmonth nvarchar(20)
 5  ,quantity int 
 6 )
 7 
 8 insert into sales
 9 select ‘p1‘,‘201401‘,100
10 union all 
11 select ‘p1‘,‘201402‘,300
12 union all 
13 select ‘p1‘,‘201403‘,500
14 union all 
15 select ‘p2‘,‘201401‘,200
16 union all 
17 select ‘p2‘,‘201402‘,300
18 union all 
19 select ‘p2‘,‘201403‘,300
20 union all 
21 select ‘p2‘,‘201401‘,300
22 union all 
23 select ‘p3‘,‘201402‘,100
24 union all 
25 select ‘p3‘,‘201403‘,300
26 
27 --select * from sales
28 
29 select ROW_NUMBER()over(order by productname,dateofmonth,quantity) sid,*
30 into #temp
31 from sales
32 
33 --select * from #temp
34 select dense_rank()over(order by productname) rid,*
35 into #temp1
36 from #temp
37 
38 select
39  a.productname
40 ,a.dateofmonth
41 ,a.quantity
42 ,sum(b.Quantity) AS TOTAL
43 from #temp1 a,#temp1 b
44 where a.sid >= b.sid and a.rid = b.rid
45 group by a.productname,a.dateofmonth,a.quantity