34,588
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE(id varchar(10),code varchar(10),price int)
insert @t select '1','A',100
UNION ALL select '2','A',200
UNION ALL select '3','B',200
UNION ALL select '4','B',200
select * from @t
select case when grouping(code)=1 then '合计' when grouping(code)=0 and grouping(id)=1 then '小计' else ISNULL(code, 'UNKNOWN') end as code,
case when grouping(id)=1 then null else ISNULL(id, 'UNKNOWN') end as id,
sum(price) as total
from @t group by code,id with rollup
结果如下:
code id total
---------- ---------- -----------
A 1 100
A 2 200
小计 NULL 300
B 3 200
B 4 200
小计 NULL 400
合计 NULL 700