SQL之GroupbywithRollup的效果
/* 建立示例过程，以演示GroupbywithRollup的效果 */ --建一临时表 createtable#tmpA (&n
/*
建立示例过程，以演示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 本文作者:佚名 来源:http://www.zxbc.cn/
CIO之家 www.ciozj.com 微信公众号:imciow
免责声明:本站转载此文章旨在分享信息，不代表对其内容的完全认同。文章来源已尽可能注明，若涉及版权问题，请及时与我们联系，我们将积极配合处理。同时，我们无法对文章内容的真实性、准确性及完整性进行完全保证，对于因文章内容而产生的任何后果，本账号不承担法律责任。转载仅出于传播目的，读者应自行对内容进行核实与判断。请谨慎参考文章信息，一切责任由读者自行承担。
延伸阅读