总计?就是除了小计之外还需要整个汇总的数据?
----------------------------------------------------------------------------
select
f1 = case
when a.f2 is null then '总计'
when a.f1 is null then '合计'
else rtrim(a.f1)
end,
f2 = case when a.f1 is null then NULL else a.f2 end,
a.f3,
a.f4,
a.f5,
a.f6
from
(select
f1,f2,f3=sum(f3),f4,f5,f6=sum(f6)
from table1
group by f2,f1,f4,f5 with rollup) a
where
a.f1 is null or a.f5 is not null
输出结果:
------------------------------------------------------------f1 f2 f3 f4 f5 f6
------------------------------------------------------------
1 A 10 e65rt tr54e 43
2 A 20 gfert tr543e 435
3 A 30 egdrt trtree 453
4 A 20 jhgert t545re 436
5 A 20 56ert tr54e 453
合计 NULL 100 NULL NULL 1820
6 B 30 654ert trrte 4783
7 B 20 uert trde 543
8 B 20 iyutert trtrete 4573
9 B 30 ytert trree 443
10 B 20 ytuert trere 463
11 B 20 euytrt trfde 473
合计 NULL 140 NULL NULL 11278
12 C 30 euyt76rt tfdre 493
13 C 20 e76rt trtye 430
14 C 20 e546rt t543re 434
15 C 30 e766rt t65re 432
16 C 20 e554rt tr56e 434
合计 NULL 120 NULL NULL 2223
总计 NULL 360 NULL NULL 15321
----------------------------------------------------------------------------
select
f1 = case
when a.f2 is null then '总计'
when a.f1 is null then '合计'
else rtrim(a.f1)
end,
f2 = case when a.f1 is null then NULL else a.f2 end,
a.f3,
a.f4,
a.f5,
a.f6
from
(select
f1,f2,f3=sum(f3),f4,f5,f6=sum(f6)
from table1
group by f2,f1,f4,f5 with rollup) a
where
a.f1 is null or a.f5 is not null
输出结果:
------------------------------------------------------------f1 f2 f3 f4 f5 f6
------------------------------------------------------------
1 A 10 e65rt tr54e 43
2 A 20 gfert tr543e 435
3 A 30 egdrt trtree 453
4 A 20 jhgert t545re 436
5 A 20 56ert tr54e 453
合计 NULL 100 NULL NULL 1820
6 B 30 654ert trrte 4783
7 B 20 uert trde 543
8 B 20 iyutert trtrete 4573
9 B 30 ytert trree 443
10 B 20 ytuert trere 463
11 B 20 euytrt trfde 473
合计 NULL 140 NULL NULL 11278
12 C 30 euyt76rt tfdre 493
13 C 20 e76rt trtye 430
14 C 20 e546rt t543re 434
15 C 30 e766rt t65re 432
16 C 20 e554rt tr56e 434
合计 NULL 120 NULL NULL 2223
总计 NULL 360 NULL NULL 15321
(Select
Case When Grouping(f2)=1 Then N'总计'
When Grouping(f1)=1 Then N'合计'
Else Rtrim(f1) End As f1,
Case When Grouping(f1)=1 Then '' Else f2 End As f2,
SUM(f3) As f3,
Case When Grouping(f1)=1 Then '' Else f4 End As f4,
Case When Grouping(f1)=1 Then '' Else f5 End As f5,
SUM(f6) As f6
from test1
Group By f2,f1,f4,f5
With Rollup) A
Where f5 Is Not Null
1 A 10 e65rt tr54e 43
2 A 20 gfert tr543e 435
3 A 30 egdrt trtree 453
4 A 20 jhgert t545re 436
5 A 20 56ert tr54e 453
合计 100 1820
6 B 30 654ert trrte 4783
7 B 20 uert trde 543
8 B 20 iyutert trtrete 4573
9 B 30 ytert trree 443
10 B 20 ytuert trere 463
11 B 20 euytrt trfde 473
合计 140 11278
12 C 30 euyt76rt tfdre 493
13 C 20 e76rt trtye 430
14 C 20 e546rt t543re 434
15 C 30 e766rt t65re 432
16 C 20 e554rt tr56e 434
合计 120 2223
总计 360 15321