SQL 之Group by with Rollup 的效果

2008-04-18 09:16:42　来源:中国自学编程网　作者:佚名点击:

建立示例过程，以演示Group by 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,

9 7 3 1 2 3 4 8 :