本文介绍如何使用SQL的GROUP BY和WITH ROLLUP进行数据汇总，包括单个多分类汇总列的合计，以及不同场景下小计与合计的实现方法。
首先创建测试表、添加数据。
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
)
情况一：只有一个分类汇总列时，只需要一个合计。只需要增加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
情况二:有多个分类汇总列，只需要一个合计.增加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
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
情况三：有多个分类汇总列，需要全部的小计和合计。
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
另外一种显示小计的方式
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
情况四：有多个分类汇总列，需要部分的小计和合计
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
select
case
when
grouping
(a)
=
1
then
'
合计
'
else
cast
(a
as
varchar
)
end
a,
case
when
grouping
(b)
=
1
and
grouping
(a)
=
0
扫一扫
4028
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
