SQL分级汇总
来源:互联网 发布:淘宝手表类目保证金 编辑:程序博客网 时间:2024/05/16 18:50
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