我希望每次用 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
该怎么做?
如:我用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
该怎么做?
FROM Inventory
GROUP BY Item, Color WITH CUBE
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
--*/
--> 生成测试数据: #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',1select 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 行受影响)
*/
from Table group by A,B,C with rollup having B is not null
drop table t1
go
create table t1(A nvarchar(10),B nvarchar(10),C nvarchar(10),value int)
insert t1 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 CASE WHEN (GROUPING(A) = 1) THEN '合計' else isnull(A,'未知') end,CASE WHEN (GROUPING(B) = 1) THEN '合計' else isnull(B,'未知') end,CASE WHEN (GROUPING(C) = 1) THEN '合計' else isnull(C,'未知') end ,sum(value) from t1 group by a,b,c with rollup
A,
B,
case when grouping(C)=0 then C
else N'合计'
end C,
sum(D) Value
from test
group by A,B,C
with rollup
having grouping(A)=0 and grouping(B)=0
如果是 求平均值 该怎么做?
如
a1 b1 c1 1
a1 b1 c2 2
a1 b1 c3 3
a1 b1 合计 (1+2+3/3行)2
B,
case when grouping(C)=0 then C else N'合计' end C,
avg(Value) Value
from #T
group by A,B,C
with rollup
having grouping(A)=0 and grouping(B)=0
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',1select A,
B,
case when grouping(C)=0 then C else N'合计' end C,
avg(Value) Value
from #T
group by A,B,C
with rollup
having grouping(A)=0 and grouping(B)=0
如:
avg
10
20
30
40
100这里的 10,20,30,40都是 求出来的平均值(avg(列))出来的该怎么做?
如:
neme avg
平均 10
平均 20
平均 30
平均 40
合计 100 这里的 10,20,30,40都是 求出来的平均值(avg(列))出来的 该怎么做?