首先创建测试表、添加数据。
create
table
#t(a
int
,b
int
,c
int
,d
int
,e
int
)
insert into #t values ( 1 , 2 , 3 , 4 , 5 )
insert into #t values ( 1 , 2 , 3 , 4 , 6 )
insert into #t values ( 1 , 2 , 3 , 4 , 7 )
insert into #t values ( 1 , 2 , 3 , 4 , 8 )
insert into #t values ( 1 , 3 , 3 , 4 , 5 )
insert into #t values ( 1 , 3 , 3 , 4 , 6 )
insert into #t values ( 1 , 3 , 3 , 4 , 8 )
insert into #t values ( 1 , 3 , 3 , 4 , 7 )
insert into #t values ( 2 , 2 , 2 , 4 , 5 )
insert into #t values ( 2 , 2 , 3 , 4 , 6 )
insert into #t values ( 2 , 2 , 4 , 4 , 7 )
insert into #t values ( 2 , 2 , 5 , 4 , 8 )
insert into #t values ( 2 , 3 , 6 , 4 , 5 )
insert into #t values ( 2 , 3 , 3 , 4 , 6 )
insert into #t values ( 2 , 3 , 3 , 4 , 8 )
insert into #t values ( 2 , 3 , 3 , 4 , 7 )
insert into #t values ( 1 , 2 , 3 , 4 , 5 )
insert into #t values ( 1 , 2 , 3 , 4 , 6 )
insert into #t values ( 1 , 2 , 3 , 4 , 7 )
insert into #t values ( 1 , 2 , 3 , 4 , 8 )
insert into #t values ( 1 , 3 , 3 , 4 , 5 )
insert into #t values ( 1 , 3 , 3 , 4 , 6 )
insert into #t values ( 1 , 3 , 3 , 4 , 8 )
insert into #t values ( 1 , 3 , 3 , 4 , 7 )
insert into #t values ( 2 , 2 , 2 , 4 , 5 )
insert into #t values ( 2 , 2 , 3 , 4 , 6 )
insert into #t values ( 2 , 2 , 4 , 4 , 7 )
insert into #t values ( 2 , 2 , 5 , 4 , 8 )
insert into #t values ( 2 , 3 , 6 , 4 , 5 )
insert into #t values ( 2 , 3 , 3 , 4 , 6 )
insert into #t values ( 2 , 3 , 3 , 4 , 8 )
insert into #t values ( 2 , 3 , 3 , 4 , 7 )
情况一:只有一个分类汇总列时,只需要一个合计。只需要增加with rollup即可。
select
case
when
grouping
(a)
=
1
then
'
合计
'
else
cast
(a
as
varchar
)
end
a,
sum (b), sum (c), sum (d), sum (e) from #t group by a with rollup
sum (b), sum (c), sum (d), sum (e) from #t group by a with rollup
情况二:有多个分类汇总列,只需要一个合计.增加rollup之后,需要增加判断。
select
case
when
grouping
(a)
=
1
then
'
合计
'
else
cast
(a
as
varchar
)
end
a,
b,
sum (c), sum (d), sum (e) from #t
group by a,b with rollup
having grouping (b) = 0 or grouping (a) = 1
b,
sum (c), sum (d), sum (e) from #t
group by a,b with rollup
having grouping (b) = 0 or grouping (a) = 1
select
case
when
grouping
(a)
=
1
then
'
合计
'
else
cast
(a
as
varchar
)
end
a,
b,
c,
sum (d), sum (e) from #t
group by a,b,c with rollup
having grouping (c) = 0 or grouping (a) = 1
b,
c,
sum (d), sum (e) from #t
group by a,b,c with rollup
having grouping (c) = 0 or grouping (a) = 1
情况三:有多个分类汇总列,需要全部的小计和合计。
select
case
when
grouping
(a)
=
1
then
'
合计
'
else
cast
(a
as
varchar
)
end
a,
case when grouping (b) = 1 and grouping (a) = 0 then ' 小计 ' else cast (b as varchar ) end b,
case when grouping (c) = 1 and grouping (b) = 0 then ' 小计 ' else cast (c as varchar ) end c,
sum (d), sum (e) from #t
group by a,b,c with rollup
case when grouping (b) = 1 and grouping (a) = 0 then ' 小计 ' else cast (b as varchar ) end b,
case when grouping (c) = 1 and grouping (b) = 0 then ' 小计 ' else cast (c as varchar ) end c,
sum (d), sum (e) from #t
group by a,b,c with rollup
另外一种显示小计的方式
select
case
when
grouping
(a)
=
1
then
'
合计
'
when grouping (b) = 1 then cast (a as varchar ) + ' 小计 '
else cast (a as varchar ) end a,
case when grouping (b) = 0 and grouping (c) = 1
then cast (b as varchar ) + ' 小计 ' else cast (b as varchar ) end b,
case when grouping (c) = 1 and grouping (b) = 0
then '' else cast (c as varchar ) end c,
sum (d), sum (e) from #t
group by a,b,c with rollup
when grouping (b) = 1 then cast (a as varchar ) + ' 小计 '
else cast (a as varchar ) end a,
case when grouping (b) = 0 and grouping (c) = 1
then cast (b as varchar ) + ' 小计 ' else cast (b as varchar ) end b,
case when grouping (c) = 1 and grouping (b) = 0
then '' else cast (c as varchar ) end c,
sum (d), sum (e) from #t
group by a,b,c with rollup
情况四:有多个分类汇总列,需要部分的小计和合计
select
case
when
grouping
(a)
=
1
then
'
合计
'
else
cast
(a
as
varchar
)
end
a,
b,
case when grouping (c) = 1 and grouping (b) = 0 then ' 小计 ' else cast (c as varchar ) end c,
sum (d), sum (e) from #t
group by a,b,c with rollup
having grouping (a) = 1 or grouping (b) = 0
b,
case when grouping (c) = 1 and grouping (b) = 0 then ' 小计 ' else cast (c as varchar ) end c,
sum (d), sum (e) from #t
group by a,b,c with rollup
having grouping (a) = 1 or grouping (b) = 0
select
case
when
grouping
(a)
=
1
then
'
合计
'
else
cast
(a
as
varchar
)
end
a,
case when grouping (b) = 1 and grouping (a) = 0
case when grouping (b) = 1 and grouping (a) = 0