SQL grouping 分组汇总

19 篇文章 0 订阅
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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值