我希望每次用 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
该怎么做?
-
SQL code
- --> 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 行受影响) */