SQL SERVER 2005 ROLLUP

--在进行sql查询的时候，有时候需要对数据进行分类汇总，就我了解，ms-sql内的分类汇总方法有ROLLUP、CUBE和COMPUTE BY。COMPUTE BY比较好理解，sql联机丛书内的说明已经说的比较清楚了，重点说明ROLLUP，ROLLUP比COMPUTE BY和CUBE更加实用和用的更为广泛。CUBE和它只是有一点区别，CUBE的结果集包含ROLLUP的结果集。
--ROLLUP和COMPUTE BY的区别：
--1。ROLLUP 返回单个结果集；COMPUTE BY 返回多个结果集。
--2。ROLLUP 可以在服务器游标中使用；COMPUTE BY 不可以。
--3。有时，查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。
--表T（a，b，c，d）
--ROLLUP在进行分类汇总的时候是很有用的，下来结合例子来说明。对于Group by a ，b， c， d with rollup。all（列）表示任意此列数据，系统将会这样进行汇总：

--a，b，c，all（d）--------相当于是group by a，b，c
--a，b，all（c），all（d）
--a，all（b），all（c），all（d）
--all（a），all（b），all（c），all（d）
--由于d with rollup，所以每次汇总都是all（d），忽视d的数据，进行汇总，然后从右到左依次忽视更多的列。由于是从右到左的，所以Group by里面的列的顺序将会影响结果。说白了select *,count(*)as '数量' Group by a ，b， c， d with rollup就“相当于”下面的语句：
select *，count(*)as '数量' from T group by a，b，c，d   --原始数据
union all
select a，b，c，‘1’，count(*)as '数量' from T group by a，b，c ---a，b，c小计
union all
select a，b，‘1’，‘1’，count(*)as '数量' from T group by a，b --a，b小计
union all
select a，‘1’，‘1’，‘1’，count(*)as '数量' from T group by a   --a小计
union all
select ‘1’，‘1’，‘1’，‘1’，count(*)as '数量' from T   --最终总计
--彩色部分就是with rollup增加的统计数据。下面分情况说明：
--情况一：只有一个分类汇总列时，只需要一个合计。只需要增加with rollup即可。
--这种情况是： group by a with rollup
--这种情况只产生一个合计列：all（b） all（C） all（d） a 按照上面的分析，实际上是
select ‘1’，‘1’，‘1’，‘1’，count(*)as '数量' from T  --多了这样的统计，也就是统计了全部数据。
--情况二：有多个分类汇总列，只需要一个合计。Group by a ，b， c， d with rollup.只需要最后的最终合计，那么就需要having
Group by a ，b， c， d with rollup
having grouping(d)=0 or grouping(a)=1
--情况三：有多个分类汇总列，需要全部的小计和合计。直接Group by a ，b， c， d with rollup，那么结果将包含上面分析的所有合计数据。
--情况四：有多个分类汇总列，需要部分的小计和合计
Group by a ，b， c， d with rollup 需要a，b，c小计
having grouping(a)=1 or grouping(c)=0
Group by a ，b， c， d with rollup 需要a，b小计
having grouping(a)=1 or (grouping(b)=0 and grouping(c)=1)
Group by a ，b， c， d with rollup 需要a小计
having grouping(a)=1 or (grouping(a)=0 and grouping(b)=1) or grouping(d)=0

--情况五：有多个分类汇总列，需要部分的小计
Group by a ，b， c， d with rollup a，b小计
having grouping(d)=0 or（grouping(b)=0 and grouping(c)=1）
--rollup的介绍到此为止，有不足之处请大家指出来。cube是不管group by里面的字段顺序进行合计的，是1个完全的组合。会出现all（a），all（a），c；all（a），b，all（c）这样的组合。
--下面是一个完整的例子，你可以在sql中运行一下：
create table tb(产品编码 varchar(10),色号 varchar(10),颜色 varchar(10))
insert into tb values('110' ,     ' ' , '白') 
insert into tb values('110' ,     '1' , '白') 
insert into tb values('110' ,     '1' , '白') 
insert into tb values('111' ,     ' ' , '白') 
insert into tb values('111' ,     '1' , ' ')
insert into tb values('111' ,     '1' , ' ')
insert into tb values('111' ,     '1' , '白') 
insert into tb values('111' ,     '1' , '白') 
insert into tb values('111' ,     '1' , '白')
go
Select 
    [产品编码2]=case when grouping([产品编码])=1 or grouping([色号])=1 then '合计' else rtrim([产品编码]) end,
    [色号2]=isnull(rtrim([色号]),''),[颜色]=isnull([颜色],''),count(*) as 数量
from  tb
group by [产品编码],[色号],[颜色] with rollup
having not (grouping([颜色])=1 and grouping([色号])=0 and grouping([产品编码])=0)
order by grouping([产品编码]),[产品编码] desc,grouping([色号]),数量 desc
--单纯的用rollup：
Select 
    case when grouping([产品编码])=1 then 'ALL' else isnull(产品编码,'UN') end as 产品编码, 
    case when grouping([色号])=1 then 'ALL' else isnull(色号,'UN') end as 色号,
    case when grouping([颜色])=1 then 'ALL' else isnull(颜色,'UN') end as 颜色,
    count(*) as 数量    
from  tb
group by [产品编码],[色号],[颜色] with rollup

--来源于 http://hi.baidu.com/david_jdai/item/d97f761b5be2b7633e87ce5a