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