分組查詢

GROUP BY 的延伸類型有：

• GROUP BY... WITH ROLLUP
• GROUP BY... WITH CUBE
• GROUP BY..., GROUPING SETS (...), ...
• GROUP BY..., CUBE (...), ...
• GROUP BY..., ROLLUP (...), ...

使用 WITH ROLLUP 和 WITH CUBE 子句

WITH ROLLUP 、 WITH CUBE 和 GROUPING 運算子是 GROUP BY 子句的擴充。

WITH ROLLUP 和 WITH CUBE 子句是 SQL2005 的新功能，這二個運算子可用來產生包含小計與總數的報告。以下是 CUBE 與 ROLLUP 之間的差異：

• WITH ROLLUP 傳回的結果集，除了 Group By 的彙總資料外，另外會針對各個欄位做階層式的彙總。
• WITH CUBE 傳回的結果集，除了 Group By 的彙總資料外，另外會針對各個欄位做排列組合式的彙總。

DECLARE @List table( 
	TypeA char(1),
	TypeB char(1),
	TypeC char(1),
	Qty int
);
insert @List values ('A','A','A',3)
insert @List values ('A','B','B',4)
insert @List values ('B','A','B',5)
insert @List values ('B','B','B',6)
insert @List values ('A','A','A',6)
insert @List values ('A','A','B',3)
insert @List values ('B','A','A',1)
insert @List values ('B','B','B',9)
insert @List values ('A','B','A',1)
insert @List values ('B','B','A',2)

SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum
FROM @List
GROUP BY TypeA,TypeB,TypeC 

SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum
FROM @List
GROUP BY TypeA,TypeB,TypeC 
WITH ROLLUP  

SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum
FROM @List
GROUP BY TypeA,TypeB,TypeC 
WITH CUBE  

注意：

• CHECKSUM_AGG 函式不支援 ROLLUP , CUBE , GROUPING SETS 。
• WITH CUBE 運算子的統計資訊會將回傳的結果集快速的澎漲，所以資料量大的查詢應避免使用。

使用 GROUPING 彙總函式

GROUPING 關鍵字在二個地方會使用到：

• 若使用在 SELECT 子句中，它是用來判別該資料列是否為小計的資訊列。
• 若使用在 GROUP BY 子句中，它是用來自訂統計的群組。

當使用 Rollup 或 Cube 時，若只看回傳結果集中的 Null 值，是無法分辯出是小計列中的 Null 還是正常資料列中的 Null。 這時候就可以用 GROUPING 函式來加以辯識是否為彙總資料欄。

若 GROUPING 回傳 1 ，表示該筆資料是以此欄位為彙總，若不是則回傳 0 。

SELECT 
	CASE WHEN (GROUPING(TypeA)=1) THEN 'don''t care' ELSE TypeA END  as TYPEA,
	CASE WHEN (GROUPING(TypeB)=1) THEN 'don''t care' ELSE TypeB END  as TYPEB,
	CASE WHEN (GROUPING(TypeC)=1) THEN 'don''t care' ELSE TypeC END  as TYPEC,
	SUM(Qty) AS QtySum
FROM @List
GROUP BY TypeA , GROUPING SETS (TypeB,TypeC)
ORDER BY TypeA,TypeB,TypeC

使用 GROUPING SETS

GROUPING SETS 是 SQL2008 的新功能。

使用 GROUPING SETS ，相同於 UNION ALL 所有的 GROUP BY 單個項目。

...
GROUP BY GROUPING SETS (C1,C2,C3) 

... GROUP BY C1
UNION ALL
... GROUP BY C2
UNION ALL
... GROUP BY C3

例如：下列項目都是對等的

例一：GROUPING SETS (單一集合)

當使用 GROUPING SETS ( () ) 時，等同於使用 GROUP BY 。

也就是說當 GROUPING SETS 中只有單一個子集合，那麼就等同 GROUP BY

使用 GROUPING SETS	使用 GROUP BY
SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum FROM @List GROUP BY GROUPING SETS ((TypeA, TypeB, TypeC))	SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum FROM @List GROUP BY TypeA, TypeB, TypeC
SELECT TypeA, TypeB, TypeC, SUM(Qty) AS QtySum FROM @List GROUP BY GROUPING SETS ((TypeA, TypeB))	SELECT TypeA, TypeB, SUM(Qty) AS QtySum FROM @List GROUP BY TypeA, TypeB

例二：GROUPING SETS (複合集合)

在 GROUPING SETS 中使用複合元素 (composite elements) 。每個複合元素會被視為一個資料行。

也就是說當 GROUPING SETS 中有多個子集合，那麼就等同 GROUP BY 各個元素後，再做 UNION ALL 。

使用 GROUPING SETS	使用 GROUP BY
SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum FROM ##List GROUP BY GROUPING SETS ( TypeA, TypeB, TypeC) --這表示三個子集合，所以等同下例 SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum FROM ##List GROUP BY GROUPING SETS ((TypeA), (TypeB), (TypeC))	SELECT TYPEA, Null as TypeB, Null as TypeC, SUM(Qty) AS QtySum FROM ##List GROUP BY TypeA UNION ALL SELECT Null as TYPEA, TypeB as TypeB, Null as TypeC, SUM(Qty) AS QtySum FROM ##List GROUP BY TypeB UNION ALL SELECT Null as TYPEA, Null as TypeB, TypeC as TypeC, SUM(Qty) AS QtySum FROM ##List GROUP BY TypeC
SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum FROM ##List GROUP BY GROUPING SETS ( TypeA, (TypeB, TypeC))	SELECT TYPEA, Null as TypeB, Null as TypeC, SUM(Qty) AS QtySum FROM ##List GROUP BY TypeA UNION ALL SELECT Null as TYPEA, TypeB as TypeB, TypeC as TypeC, SUM(Qty) AS QtySum FROM ##List GROUP BY TypeB, TypeC

例三

由前面介紹可以知道， ROLLUP 或 CUBE 可以在結果集中加入小計的資料列，但是如果你不需要那麼多種組合的小計，就可以使用 GROUPING SETS 只指定您想要的群組。

例如，上面例子中，若只想取得 TypeA 的小計，就可以這麼使用：

--使用 ROLLUP 會產生所有可能的群組小計
SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum
FROM ##List
GROUP BY ROLLUP (TypeA,TypeB,TypeC)

--使用 GROUPING SETS 可以指定群組做小計
SELECT TypeA,TypeB,TypeC,SUM(Qty) AS QtySum
FROM ##List
GROUP BY GROUPING SETS ((TypeA,TypeB,TypeC),(TypeC),())