我希望每次用 GROUP BY 排序完都加 相应的合计该怎么做?
如:我用select A,B,C from Table group by A,B,C查出结果为
A B C VALUE
a1 b1 c1 1
a1 b1 c2 2
a1 b1 c3 3
a1 b2 c2 2
a1 b2 c3 3
a1 b2 c1 1
a2 b1 c1 1
a2 b1 c2 2
a2 b1 c3 3
a2 b2 c3 3
a2 b2 c2 2
a2 b2 c1 1
我希望查出
A B C VALUE
a1 b1 c1 1
a1 b1 c2 2
a1 b1 c3 3
a1 b1 合计 6
a1 b2 c2 2
a1 b2 c3 3
a1 b2 c1 1
a1 b2 合计 6
a2 b1 c1 1
a2 b1 c2 2
a2 b1 c3 3
a2 b1 合计 6
a2 b2 c3 3
a2 b2 c2 2
a2 b2 c1 1
a2 b2 合计 6
该怎么做?
DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int) 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 --*/
--> liangCK小梁 于2008-07-22 --> 生成测试数据: #T if object_id('tempdb.dbo.#T') is not null drop table #T create table #T (A varchar(2),B varchar(2),C varchar(2),VALUE int) insert into #T select 'a1','b1','c1',1 union all select 'a1','b1','c2',2 union all select 'a1','b1','c3',3 union all select 'a1','b2','c2',2 union all select 'a1','b2','c3',3 union all select 'a1','b2','c1',1 union all select 'a2','b1','c1',1 union all select 'a2','b1','c2',2 union all select 'a2','b1','c3',3 union all select 'a2','b2','c3',3 union all select 'a2','b2','c2',2 union all select 'a2','b2','c1',1 select A, B, case when grouping(C)=0 then C else N'合计' end C, sum(Value) Value from #T group by A,B,C with rollup having grouping(A)=0 and grouping(B)=0 /* A B C Value ---- ---- ---- ----------- a1 b1 c1 1 a1 b1 c2 2 a1 b1 c3 3 a1 b1 合计 6 a1 b2 c1 1 a1 b2 c2 2 a1 b2 c3 3 a1 b2 合计 6 a2 b1 c1 1 a2 b1 c2 2 a2 b1 c3 3 a2 b1 合计 6 a2 b2 c1 1 a2 b2 c2 2 a2 b2 c3 3 a2 b2 合计 6 (16 行受影响) */