code price
--------- -----------
A 100
B 200
使用 select code,sum(price) as total from 表名 group by code with rollup 后得到
code price
--------- -----------
A 100
B 200
null 300
我想要的结果如下,在每一类code下有一小计,如何实现
code price
--------- -----------
A 100
null 100
B 200
null 200
null 300
--------- -----------
A 100
B 200
使用 select code,sum(price) as total from 表名 group by code with rollup 后得到
code price
--------- -----------
A 100
B 200
null 300
我想要的结果如下,在每一类code下有一小计,如何实现
code price
--------- -----------
A 100
null 100
B 200
null 200
null 300
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red', -23
UNION ALL SELECT 'bb','Cup' ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red', -90--汇总显示
SELECT Groups=CASE
WHEN GROUPING(Color)=0 THEN Groups
WHEN GROUPING(Groups)=1 THEN '总计'
ELSE '' END,
Item=CASE
WHEN GROUPING(Color)=0 THEN Item
WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'
ELSE '' END,
Color=CASE
WHEN GROUPING(Color)=0 THEN Color
WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'
ELSE '' END,
Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
/*--结果
Groups Item Color Quantity
-------- ---------------- ---------------------- -----------
aa Chair Blue 101
aa Chair Red -90
Chair 小计 11
aa Table Blue 124
Table 小计 124
aa 合计 135
bb Cup Green -23
Cup 小计 -23
bb Table Red -23
Table 小计 -23
bb 合计 -46
总计 89
--*/
insert @t select '1','A',100
UNION ALL select '2','A',200
UNION ALL select '3','B',200
UNION ALL select '4','B',200select * 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