--临时表创建
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)

insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)

insert into #t values(3,4,9,4,7)

select * from #t


-- 一：只有一个分类汇总列时，只需要一个合计,只需要增加with rollup即可

select case when grouping(a) = 1 then '合计' else cast(a as varchar) end as a,
	sum(b),sum(c),sum(d),sum(e) from #t group by a with rollup

-- 二:有多个分类汇总列，只需要一个合计.增加rollup之后，需要增加判断

select case when grouping(a) = 1 then '合计' else cast(a as varchar) end as a,b,
	sum(c),sum(d),sum(e) from #t group by a,b with rollup
	having grouping(b) = 0 or grouping(a) = 1


select case when grouping(a)=1 then '合计' else cast(a as varchar) end a,
    b,
    c,
sum(d),sum(e) from #t 
group by a,b,c with rollup 
having grouping(c)=0 or grouping(a)=1


-- 三：有多个分类汇总列，需要全部的小计和合计。

select case when grouping(a) = 1 then '合计' else cast(a as varchar) end as a,
       case when grouping(b) = 1 and grouping(a) = 0 then '小计' else cast (b as varchar) end as b,
       case when grouping(c) = 1 and grouping(b) = 0 then '小计' else cast (c as varchar) end as c,
	sum(d),sum(e) from #t group by a,b,c with rollup


-- 四：有多个分类汇总列，需要部分的小计和合计

select case when grouping(a) = 1 then '合计' else cast(a as varchar) end a,b,
       case when grouping(c) = 1 and grouping(b) = 0 then '小计' else cast(c as varchar) end c,
       sum(d),sum(e) from #t 
	group by a,b,c with rollup 
	having grouping(a) = 1 or grouping(b) = 0

select case when grouping(a) = 1 then '合计' else cast(a as varchar) end a,
       case when grouping(b) = 1 then '总计' else cast(b as varchar) end b,
       isnull((case when grouping(c) = 1 and grouping(b)=0 then '小计' else cast(c as varchar) end),'') c,
       sum(d),sum(e) from #t 
	group by a,b,c with rollup 

转载留着自己学习用（http://www.cnblogs.com/yiway/archive/2007/11/12/957281.html）

发表于 @ 2008年06月06日　10:55:00 | 评论( loading... ) | 编辑| 举报| 收藏

旧一篇:Sql + rollup + cube