declare
@tab
table
(groups
varchar
(
10
),
group1
varchar
(
10
),
group2
varchar
(
10
),
group3
decimal
(
10
,
2
))
Insert
Into
@tab
Select
'
aa
'
,
'
bb
'
,
'
cc
'
,
12
union
all
Select
'
aa
'
,
'
bb
'
,
'
cd
'
,
13
union
all
Select
'
aa
'
,
'
bb
'
,
'
dd
'
,
14
union
all
Select
'
aa
'
,
'
bb
'
,
'
de
'
,
15
union
all
Select
'
aa
'
,
'
ff
'
,
'
ca
'
,
16
union
all
Select
'
aa
'
,
'
ff
'
,
'
cc
'
,
17
union
all
Select
'
gg
'
,
'
bb
'
,
'
cc
'
,
18
union
all
Select
'
gg
'
,
'
af
'
,
'
cc
'
,
19
union
all
Select
'
gg
'
,
'
af
'
,
'
cf
'
,
23
Select
(
Case
When
Grouping
(groups)
=
0
and
Grouping
(group1)
=
0
and
Grouping
(group2)
=
0
Then
groups
When
Grouping
(groups)
=
1
Then
'
总计
'
Else
''
End
),
(
Case
When
Grouping
(group1)
=
0
and
grouping
(group2)
=
0
Then
group1
When
Grouping
(group1)
=
1
And
Grouping
(groups)
=
0
Then
'
Group1小计
'
Else
''
End
),
(
Case
When
Grouping
(Group2)
=
0
Then
group2
When
Grouping
(Group2)
=
1
And
Grouping
(group1)
=
0
Then
'
Group2小计
'
Else
''
End
),
Sum
(group3)
From
@tab
Group
BY
Groups,Group1,Group2
With
Rollup
Order
BY
Grouping
(Groups),Groups,
Grouping
(group1),Group1,
Grouping
(Group2),Group2
结果
aa bb cc
12.00
aa bb cd
13.00
aa bb dd
14.00
aa bb de
15.00
Group2小计
54.00
aa ff ca
16.00
aa ff cc
17.00
Group2小计
33.00
Group1小计
87.00
gg af cc
19.00
gg af cf
23.00
Group2小计
42.00
gg bb cc
18.00
Group2小计
18.00
Group1小计
60.00
总计
147.00