SQL Server WITH ROLLUP、WITH CUBE、GROUPING语句的应用

半只蚂蚁

　　CUBE：CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
　　ROLLUP：ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
　　GROUPING：当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。
　　先创建一个临时表：
　　

create table #temp　　
(
　　
     姓名    varchar(50) not null,
　　
     课程    varchar(50)    null,
　　
     分数    int        null
　　
)
　　

　　
insert into #temp
　　
select '小红','SQL','85' union
　　
select '小红','C#','90' union
　　
select '小明','SQL','93' union
　　
select '小明','C#','82' union
　　
select '小李','SQL','88' union
　　
select '小李','C#',null
　　

　　
select * from #temp
　　

　　WITH CUBE：
　　

select 姓名,课程,sum(分数)　　

from #temp　　
group by 姓名,课程
　　
with cube
　　

　　先以姓名分组和课程组合，再以姓名和课程分组进行组合。
　　PS：分类依据并不是根据select 中的顺序,而是根据group by中的顺序。
　　下面换个顺序看看结果：
　　

select 姓名,课程,sum(分数)　　

from #temp　　
group by 课程,姓名
　　
with cube
　　

　　先以课程分组和姓名组合，再以课程和姓名分组进行组合。
　　CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
　　WITH ROLLUP：
　　

select 姓名,课程,sum(分数)　　

from #temp　　
group by 姓名,课程
　　
with rollup
　　

　　

select 姓名,课程,sum(分数)　　

from #temp　　
group by 课程,姓名
　　
with rollup
　　

　　ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
　　那么这个某一层次结构是什么呢？看一下上面的数据，当以姓名先分组时，分成了三组（不计最后一行合计），当以课程先分组时，分成了两组（不计最后一行合计）。
　　这个某一层次结构我猜想应该跟 group by 的分组顺序有关。
　　GROUPING：
　　grouping 与 with rollup 的结合（与with cube的结合是一样的）
　　

select 姓名,课程,sum(分数),GROUPING(姓名)　　

from #temp　　
group by 姓名,课程
　　
with rollup
　　

　　当 grouping 指定列为【姓名】时，只有最后一行是 with rollup 所添加的。
　　

select 姓名,课程,sum(分数),GROUPING(课程)　　

from #temp　　
group by 姓名,课程
　　
with rollup
　　

　　当 grouping 指定列为【课程】时，第三行、第六行、第九行和最后一行是 with rollup 所添加的。
　　当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。
　　

select 姓名,　　

case when GROUPING(姓名)=1　　
      then '总计'
　　
      else
　　
         case when GROUPING(课程)=1
　　
              then '小计'
　　
              else 课程 end  
　　
      end 课程,
　　
sum(分数)
　　
from #temp
　　
group by 姓名,课程
　　
with rollup