SQL 之Group by with Rollup 的效果

本文通过创建示例数据库表并填充数据,详细展示了如何使用 SQL 的 Group By with Rollup 子句来实现数据汇总及不同层级的总计计算。通过对结果集的进一步处理,实现了更友好的数据展示形式。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

/*
建立示例过程,以演示Group by with Rollup 的效果
*/

-- 建一临时表
create table #tmpA
( Dept char(3) , Sect char(3) , line char(3) , Line_Desc varchar(30) , Title_code char(3) , Title_Desc varchar(30) ,  Headcount int
)

-- 加入一些数据记录
Insert #tmpA  Select 'DA' , 'S1' , 'La' , 'Line a ' , 'T1' , 'Title Desc 1', 3
Insert #tmpA  Select 'DA' , 'S1' , 'La' ,  'Line a ' , 'T2' ,  'Title Desc 2' ,  3
Insert #tmpA  Select 'DA' , 'S1' , 'Lb' , 'Line b ' ,  'T1' ,  'Title Desc 1' , 3
Insert #tmpA  Select 'DA' , 'S1' , 'Lb' , 'Line b ' ,  'T2' ,  'Title Desc 2' ,  3
Insert #tmpA  Select 'DA' , 'S2' , 'La' , 'Line a ' ,  'T1' ,   'Title Desc 1' , 3
Insert #tmpA  Select 'DA' , 'S2' , 'Lb' , 'Line b ' ,  'T1' ,   'Title Desc 1' , 3
Insert #tmpA  Select 'DB' , 'S1' , 'Lb' , 'Line b ' ,  'T1' ,   'Title Desc 1' , 3
Insert #tmpA  Select 'DB' , 'S2' , 'La' , 'Line a ' ,  'T1' ,   'Title Desc 1' , 3
Insert #tmpA  Select 'DB' , 'S2' , 'Lb' , 'Line b ' ,  'T1' ,   'Title Desc 1' , 3
Insert #tmpA  Select 'DC' , 'S1' , 'La' , 'Line a ' ,  'T1' ,   'Title Desc 1' , 3
Insert #tmpA  Select 'DC' , 'S2' , 'Lb' , 'Line b ' ,  'T1' ,   'Title Desc 1' , 3

select * from #tmpA

-- 直接使用的效果
select dept , sect , line , title_code , max(title_desc) as title_desc , sum(headcount) as Headcounts
 from #tmpA
 group by dept , sect , line , title_code with rollup

-- 对其中一个进行转换标题的效果
select dept , sect , line , ( case  when title_code is null then 'total ( by Line ) : ' else title_code end )  as title_code, sum(headcount) as Headcounts
 from #tmpA
 group by dept , sect , line , title_code with rollup

-- 对其中一个进行转换标题的增强效果
select dept , sect , line ,
 ( case  when (title_code is null) and ( line is not null) then 'total ( by Line ) : ' else title_code end )  as title_code,
 sum(headcount) as Headcounts
 from #tmpA
 group by dept , sect , line , title_code with rollup

-- 对所有的进行处理后的结果
select 
 ( case  when (dept is null) then 'Grant total  : ' else isNull(dept,'')  end )  as sect,
 ( case  when (sect is null) and ( dept is not null) then 'total ( by Dept ) : ' else IsNull(sect,'') end )  as sect,
 ( case  when (line is null) and ( sect is not null) then 'total ( by Sect ) : ' else IsNull(line,'') end )  as line,
 ( case  when (title_code is null) and ( line is not null) then 'total ( by Line ) : ' else isNull(title_code,'') end )  as title_code,
 sum(headcount) as Headcounts
 from #tmpA
 group by dept , sect , line , title_code with rollup

-- 对所有的进行处理后的结果
select 
 ( case  when (dept is null) then 'Grand total  : ' else isNull(dept,'')  end )  as sect,
 ( case  when (sect is null) and ( dept is not null) then 'Total ( by Dept  : ' + dept + ') : ' else IsNull(sect,'') end )  as sect,
 ( case  when (line is null) and ( sect is not null) then 'Total ( by Sect : ' + Sect + ' ) : ' else IsNull(line,'') end )  as line,
 ( case  when (title_code is null) and ( line is not null) then '小计 ( 按组别  : ' + line + ') : ' else isNull(title_code,'') end )  as title_code,
 sum(headcount) as Headcounts
 from #tmpA
 group by dept , sect , line , title_code with rollup

select 
 ( case  when (dept is null) then 'Grant total  : ' else isNull(dept,'')  end )  as sect,
 ( case  when (sect is null) and ( dept is not null) then 'total ( by Dept ) : ' else IsNull(sect,'') end )  as sect,
 max(line) , max(title_code) ,
 sum(headcount) as Headcounts
 from #tmpA
 group by dept , sect  with rollup
 

sect            sect                           Headcounts 
--------------- -------------------- ---- ---- -----------
DA              S1                   Lb   T2   12
DA              S2                   Lb   T1   6
DA              total ( by Dept ) :  Lb   T2   18
DB              S1                   Lb   T1   3
DB              S2                   Lb   T1   6
DB              total ( by Dept ) :  Lb   T1   9
DC              S1                   La   T1   3
DC              S2                   Lb   T1   3
DC              total ( by Dept ) :  Lb   T1   6
Grant total  :                       Lb   T2   33

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值