主要是利用聚合函数通过自连接去实现分组逐行计算某列的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
浙公网安备 33010602011771号