SQL SERVER中GROUPING SETS，CUBE，ROLLUP

SQL SERVER中GROUPING SETS，CUBE，ROLLUP
前言
全文我們將使用一個CTE語句作為基礎資料:

with testas(    select N'LeeWhoeeUnisersity' as name,N'資料庫' as category, 30 as totalcount    union all    select N'LeeWhoeeUnisersity','.NET',20    union all    select N'DePaul',N'.NET',40    union all    select N'DePaul',N'WEB設計',30)select * from test

結果集是：

name                             category       totalcount
LeeWhoeeUnisersity  資料庫                30 
LeeWhoeeUnisersity  .NET                  20 
DePaul                          .NET                  40 
DePaul                       WEB設計              30 

GROUPING SETS使用 GROUPING SETS 的 GROUP BY 子句可以產生一個等效於由多個簡單 GROUP BY 子句的 UNION ALL 產生的結果集。

SELECT customer, year, SUM(sales)FROM TGROUP BY GROUPING SETS ((customer), (year))和SELECT customer, NULL as year, SUM(sales)FROM T GROUP BY customerUNION ALLSELECT NULL as customer, year, SUM(sales)FROM T GROUP BY year是等效的。

看執行個體：

with testas(    select N'LeeWhoeeUnisersity' as name,N'資料庫' as category, 30 as totalcount    union all    select N'LeeWhoeeUnisersity','.NET',20    union all    select N'DePaul',N'.NET',40    union all    select N'DePaul',N'WEB設計',30)select name,category,sum(totalcount) as [sum] from testgroup by grouping sets ((name),(category))

結果：

name	category	sum
	.NET	60
	WEB設計	30
	資料庫	30
DePaul		70
LeeWhoeeUnisersity		50

ROLLUP

GROUP BY ROLLUP (C1, C2, …, Cn-1, Cn)或者GROUP BY C1, C2, …, Cn-1, Cn WITH ROLLUP和 GROUP BY GROUPING SETS ( (C1, C2, …, Cn-1, Cn)    ,(C1, C2, ..., Cn-1)    ...    ,(C1, C2)    ,(C1)    ,() )是等效的。注意WITH ROLLUP是舊版本的寫法，GROUP BY ROLLUP 只能運行於相容性100以上的版本。

執行個體：

with testas(    select N'LeeWhoeeUnisersity' as name,N'資料庫' as category, 30 as totalcount    union all    select N'LeeWhoeeUnisersity','.NET',20    union all    select N'DePaul',N'.NET',40    union all    select N'DePaul',N'WEB設計',30)select name,category,sum(totalcount) as [sum] from testgroup by rollup (name,category)

相當於

with testas(    select N'LeeWhoeeUnisersity' as name,N'資料庫' as category, 30 as totalcount    union all    select N'LeeWhoeeUnisersity','.NET',20    union all    select N'DePaul',N'.NET',40    union all    select N'DePaul',N'WEB設計',30)select name,category,sum(totalcount) as [sum] from testgroup by grouping sets ((name,category),(name),())

結果：

name	category	sum
DePaul	.NET	40
DePaul	WEB設計	30
DePaul		70
LeeWhoeeUnisersity	.NET	20
LeeWhoeeUnisersity	資料庫	30
LeeWhoeeUnisersity		50
		120

ROLLUP 產生的結果集顯示了所選列中值的某一階層的彙總，如“DePaul 70，LeeWhoeeUniversity 50”。(........,())最後一對小括弧()表示進行全部匯總，即結果中的120。
CUBE

GROUP BY CUBE (C1, C2, C3)
等效於
GROUP BY GROUPING SETS ( (C1, C2, C3)    ,(C1, C2)    ,(C1, C3)    ,(C2, C3)    ,(C1)    ,(C2)    ,(C3)    ,() )

進行CUBE測試：

with testas(    select N'LeeWhoeeUnisersity' as name,N'資料庫' as category, 30 as totalcount    union all    select N'LeeWhoeeUnisersity','.NET',20    union all    select N'DePaul',N'.NET',40    union all    select N'DePaul',N'WEB設計',30)select case when grouping(name)=1 then 'allnames' else name end as name,case when grouping(category)=1 then 'allcategories' else category end as category,sum(totalcount) as sumfrom testgroup by cube(name,category)

相當於

with testas(    select N'LeeWhoeeUnisersity' as name,N'資料庫' as category, 30 as totalcount    union all    select N'LeeWhoeeUnisersity','.NET',20    union all    select N'DePaul',N'.NET',40    union all    select N'DePaul',N'WEB設計',30)select case when grouping(name)=1 then 'allnames' else name end as name,case when grouping(category)=1 then 'allcategories' else category end as category,sum(totalcount) as sumfrom testgroup by grouping sets((name,category),(name),(category),())

結果：
name                                   category               sum
LeeWhoeeUnisersity            .NET                    20
DePaul                                 .NET                    40
allnames                              .NET                    60
DePaul                             WEB設計                 30
allnames                          WEB設計                 30
LeeWhoeeUnisersity           資料庫                  30
allnames                             資料庫                  30
allnames                            allcategories          120
LeeWhoeeUnisersity         allcategories          50
DePaul                               allcategories          70

CUBE 產生的結果集顯示了所選列中值的所有組合的彙總。

彙總函式GROUPING: 當用當結果集中的行是由GROUPING SETS，CUBE，或ROLLUP產生的，則值為1否則為0。

如果沒有用CASE WHEN判斷GROUPING，則上面所有的allnames，allcategories會被NULL替代。