首页 > 代码库 > SQL Server WITH ROLLUP、WITH CUBE、GROUPING语句的应用
SQL Server WITH ROLLUP、WITH CUBE、GROUPING语句的应用
CUBE：CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP：ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
GROUPING：当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。
先创建一个临时表：
1 create table #temp 2 ( 3 姓名 varchar(50) not null, 4 课程 varchar(50) null, 5 分数 int null 6 ) 7 8 insert into #temp 9 select ‘小红‘,‘SQL‘,‘85‘ union 10 select ‘小红‘,‘C#‘,‘90‘ union 11 select ‘小明‘,‘SQL‘,‘93‘ union 12 select ‘小明‘,‘C#‘,‘82‘ union 13 select ‘小李‘,‘SQL‘,‘88‘ union 14 select ‘小李‘,‘C#‘,null 15 16 select * from #temp
WITH CUBE：
1 select 姓名,课程,sum(分数) 2 from #temp 3 group by 姓名,课程 4 with cube
先以姓名分组和课程组合，再以姓名和课程分组进行组合。
PS：分类依据并不是根据select 中的顺序,而是根据group by中的顺序。
下面换个顺序看看结果：
1 select 姓名,课程,sum(分数) 2 from #temp 3 group by 课程,姓名 4 with cube
先以课程分组和姓名组合，再以课程和姓名分组进行组合。
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
WITH ROLLUP：
1 select 姓名,课程,sum(分数) 2 from #temp 3 group by 姓名,课程 4 with rollup
1 select 姓名,课程,sum(分数) 2 from #temp 3 group by 课程,姓名 4 with rollup
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
那么这个某一层次结构是什么呢？看一下上面的数据，当以姓名先分组时，分成了三组（不计最后一行合计），当以课程先分组时，分成了两组（不计最后一行合计）。
这个某一层次结构我猜想应该跟 group by 的分组顺序有关。
GROUPING：
grouping 与 with rollup 的结合（与with cube的结合是一样的）
1 select 姓名,课程,sum(分数),GROUPING(姓名) 2 from #temp 3 group by 姓名,课程 4 with rollup
当 grouping 指定列为【姓名】时，只有最后一行是 with rollup 所添加的。
1 select 姓名,课程,sum(分数),GROUPING(课程) 2 from #temp 3 group by 姓名,课程 4 with rollup
当 grouping 指定列为【课程】时，第三行、第六行、第九行和最后一行是 with rollup 所添加的。
当行由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 1；当行不由 CUBE 或 ROLLUP 运算符添加时，该函数将导致附加列的输出值为 0。
1 select 姓名, 2 case when GROUPING(姓名)=1 3 then ‘总计‘ 4 else 5 case when GROUPING(课程)=1 6 then ‘小计‘ 7 else 课程 end 8 end 课程, 9 sum(分数) 10 from #temp 11 group by 姓名,课程 12 with rollup
SQL Server WITH ROLLUP、WITH CUBE、GROUPING语句的应用