SQL grouping 分组汇总

Create Table T_SendMoney(StateCode varchar(6),DepCode varchar(6),SendMoney Money)
Insert Into T_SendMoney
Select '100001','310001',1000
UNION ALL
Select '100001','310001',2000
UNION ALL
Select '100001','310002',1500
UNION ALL
Select '100002','320001',3000
UNION ALL
Select '100002','320001',1200
UNION ALL
Select '100003','330001',1800
UNION ALL
Select '100003','330002',2100
UNION ALL
Select '100004','340001',2500

select * from T_SendMoney

--按grouping 来实现分级汇总数据
select case when grouping(statecode)=1 then 'total:' else statecode end as statecode
    ,case when grouping(depcode)=1 then 'state total:' else depcode end as depcode
    ,sum(sendmoney) as sendmoney
from t_sendmoney
group by statecode,depcode with rollup

--查询结果　　

StateCode DepCode SendMoney　　-----------------------------------------　　

100001 310001 3000.00　　

100001 310002 1500.00　　

100001 State Total: 4500.00　　

100002 320001 4200.00　　

100002 State Total: 4200.00　　

100003 330001 1800.00　　

100003 330002 2100.00　　

100003 State Total: 3900.00　　

100004 340001 2500.00　　

100004 State Total: 2500.00　　

Total: State Total: 15100.00