搭配 ROLLUP、CUBE 和 GROUPING SETS 使用 GROUP BY

ROLLUP、CUBE 和 GROUPING SETS 運算子是 GROUP BY 子句的擴充。ROLLUP、CUBE 或 GROUPING SETS 運算子可以產生與使用 UNION ALL 結合單一群組查詢時相同的結果；不過，如果只使用 GROUP BY 運算子的其中之一，通常更有效率。

GROUPING SETS 運算子產生的結果集，與使用簡單 GROUP BY、ROLLUP 或 CUBE 運算子所產生的相同。當不需要使用完整 ROLLUP 或 CUBE 運算子產生的所有其他群組時，您可以使用 GROUPING SETS，只指定您想要的群組。GROUPING SETS 清單可包含重複群組；而且，當 GROUPING SETS 搭配 ROLLUP 和 CUBE 使用時，它可能也會產生重複群組。只要使用 UNION ALL，重複群組就會保留原樣。

[!附註]

CUBE、ROLLUP 和 GROUPING SETS 不支援 CHECKSUM_AGG 函數。

複合與串連元素

在GROUPING SETS 清單中，括號內的多個資料行都視為單一的集。例如，在子句 GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4) 中，Column1 和 Column2 視為一個資料行。如需如何搭配複合元素使用 GROUPING SETS 的範例，請參閱本主題稍後的範例 H。

當 GROUPING SETS 清單在括號內包含多個集時，集的輸出會串連起來，以逗號分隔。結果集是群組集的交叉乘積或笛卡兒乘積。如需如何搭配串連 ROLLUP 運算使用 GROUP BY 的範例，請參閱本主題稍後的範例 D。

比較 OLAP 維度的 ROLLUP 和 CUBE

使用 ROLLUP 和 CUBE 運算子的查詢，會產生某些與 OLAP 應用程式相同的結果集，並執行某些相同的計算。CUBE 運算子會產生可用於跨表格式報表的結果集。ROLLUP 運算可計算相當於 OLAP 維度或階層架構的結果。

例如，給定具有年、月，以及日階層或屬性的時間維度，下列 ROLLUP 運算會產生下列群組。

運算	群組
ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	year, month, day year, month year ()

給定串連年、月，以及日層級的時間維度，並同時具有地區和城市層級的位置維度的下列 ROLLUP 作業，會輸出下列群組。

運算	群組
ROLLUP (region, city), ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	region, city, year, month, day region, city, year, month region, city, year region, city region, year, month, day region, year, month region, year region year, month, day year, month year ()

位置與時間維度皆為同樣層級的 CUBE 作業，會輸出下列群組。

運算

群組

CUBE (region, city ,DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))

region, city, year, month, day region, city, year, month region, city, year region, city region, city, month, day region, city, month region, city, day region, city, year, day region, city, day region, year, month, day region, year, month region, year region, month, day region, month region, year, day region, day region city, year, month, day city, year, month city, year city, month, day city, month city, year, day city, day year, month, day year, month year year, day month, day month day ()

結果集中的 NULL

在由 GROUP BY 運算子產生的結果集中，NULL 有下列用途：

• 如果群組資料行包含 NULL，系統會把所有 null 值都視為相等，並將它們放入一個 NULL 群組中。

• 當資料行在資料列中進行彙總，該資料行的值就會顯示為 NULL。

下列範例使用 GROUPING 功能，顯示 NULL 的兩種用途。在資料行中的 null 都已分組後，資料列中的 UNKNOWN 會取代 NULL。在 NULL 指示彙總中已包含某個資料行後，資料行中的 ALL 會取代 NULL。

USE tempdb;
GO
CREATE TABLE dbo.GroupingNULLS (
    Store nvarchar(19)
    ,SaleYear nvarchar(4)
    ,SaleMonth nvarchar (7))
INSERT INTO dbo.GroupingNULLS VALUES
(NULL,NULL,'January')
,(NULL,'2002',NULL)
,(NULL,NULL,NULL)
,('Active Cycling',NULL ,'January')
,('Active Cycling','2002',NULL)
,('Active Cycling',NULL ,NULL)
,('Active Cycling',NULL,'January')
,('Active Cycling','2003','Febuary')
,('Active Cycling','2003',NULL)
,('Mountain Bike Store','2002','January')
,('Mountain Bike Store','2002',NULL)
,('Mountain Bike Store',NULL,NULL)
,('Mountain Bike Store','2003','January')
,('Mountain Bike Store','2003','Febuary')
,('Mountain Bike Store','2003','March');

SELECT ISNULL(Store,
    CASE WHEN GROUPING(Store) = 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS Store
    ,ISNULL(CAST(SaleYear AS nvarchar(7)),
    CASE WHEN GROUPING(SaleYear)= 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS SalesYear
    ,ISNULL(SaleMonth,
    CASE WHEN GROUPING(SaleMonth) = 0 THEN 'UNKNOWN' ELSE 'ALL'END)
    AS SalesMonth
    ,COUNT(*) AS Count
FROM dbo.GroupingNULLS 
GROUP BY ROLLUP(Store, SaleYear, SaleMonth);

以下為結果集：

Store	SalesYear	SalesMonth	Count
Unknown	Unknown	Unknown	1
Unknown	Unknown	January	1
Unknown	Unknown	ALL	2
Unknown	2002	Unknown	1
Unknown	2002	ALL	1
Unknown	ALL	ALL	3
Active Cycling	Unknown	Unknown	1
Active Cycling	Unknown	January	2
Active Cycling	Unknown	ALL	3
Active Cycling	2002	Unknown	1
Active Cycling	2002	ALL	1
Active Cycling	2003	Unknown	1
Active Cycling	2003	February	1
Active Cycling	2003	ALL	2
Active Cycling	ALL	ALL	6
Mountain Bike Store	Unknown	Unknown	1
Mountain Bike Store	Unknown	ALL	1
Mountain Bike Store	2002	Unknown	1
Mountain Bike Store	2002	January	1
Mountain Bike Store	2002	ALL	2
Mountain Bike Store	2003	February	1
Mountain Bike Store	2003	January	1
Mountain Bike Store	2003	March	1
Mountain Bike Store	2003	ALL	3
Mountain Bike Store	ALL	ALL	6
ALL	ALL	ALL	15

範例

本節中的範例使用 SUM 彙總函式，如此就能比較結果集。也可以用其他彙總函式來計算不同的摘要。

A. 使用簡單的 GROUP BY

在下列範例中，簡單的 GROUP BY 傳回的結果集，可與範例 B 到 K 的結果集進行比較。這些範例使用具有相同 SELECT 陳述式的 GROUP BY 運算子。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID
ORDER BY T.[Group], T.CountryRegionCode
    ,S.Name,H.SalesPersonID;

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
Europe	DE	Versatile Sporting Goods Company	284	859.232
Europe	DE	Versatile Sporting Goods Company	289	17691.83
Europe	FR	Spa and Exercise Outfitters	284	32774.36
Europe	FR	Spa and Exercise Outfitters	286	246272.4

B. 使用 GROUP BY ROLLUP

在下列範例中，ROLLUP 運算子會傳回包含下列群組的結果集：

• Region、Country、Store 和 SalesPersonID

• Region、Country 和 Store

• Region和 Country

• Region

• 總計

由 ROLLUP 所產生的群組數目，等於在 ROLLUP 清單中的資料行加上總計群組的數目。群組中的資料列數，由群組資料行中的唯一組合值的數目來決定。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales' 
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
Europe	NULL	NULL	NULL	297597.8
Europe	DE	NULL	NULL	18551.07
Europe	DE	Versatile Sporting Goods Company	NULL	18551.07
Europe	DE	Versatile Sporting Goods Company	284	859.232
Europe	DE	Versatile Sporting Goods Company	289	17691.83
Europe	FR	NULL	NULL	279046.8
Europe	FR	Spa and Exercise Outfitters	NULL	279046.8
Europe	FR	Spa and Exercise Outfitters	284	32774.36
Europe	FR	Spa and Exercise Outfitters	286	246272.4

C. 搭配相反資料行順序使用 GROUP BY ROLLUP

在下列範例中，ROLLUP 運算子會傳回包含下列群組的結果集：

• SalesPersonID、Store、Country 和 Region

• SalesPersonID、Store 和 Country

• SalesPersonID和Store

• SalesPersonID

• 總計

在 ROLLUP 清單中的資料行與在範例 B 中的相同，只是順序相反。資料行是由左到右積存，因此，順序會影響到群組。在結果集中的資料列數可能與資料行順序不同。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group])
ORDER BY H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group];

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	Spa and Exercise Outfitters	284	32774.36
NULL	FR	Spa and Exercise Outfitters	284	32774.36
Europe	FR	Spa and Exercise Outfitters	284	32774.36
NULL	NULL	Versatile Sporting Goods Company	284	859.232
NULL	DE	Versatile Sporting Goods Company	284	859.232
Europe	DE	Versatile Sporting Goods Company	284	859.232
NULL	NULL	NULL	286	246272.4
NULL	NULL	Spa and Exercise Outfitters	286	246272.4
NULL	FR	Spa and Exercise Outfitters	286	246272.4
Europe	FR	Spa and Exercise Outfitters	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Versatile Sporting Goods Company	289	17691.83
NULL	DE	Versatile Sporting Goods Company	289	17691.83
Europe	DE	Versatile Sporting Goods Company	289	17691.83

D. 搭配串連的 ROLLUP 作業使用 GROUP BY

在下列範例中，傳回兩項 ROLLUP 作業的交叉乘積。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
GROUP BY 
    ROLLUP(T.[Group], T.CountryRegionCode)
    ,ROLLUP(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

以下為結果集：

Region	Country	Year	Month	Total Sales
NULL	NULL	NULL	NULL	966221.9606
NULL	NULL	2006	NULL	966221.9606
NULL	NULL	2006	7	109936.0248
NULL	NULL	2006	8	296651.4808
NULL	NULL	2006	9	184477.7563
NULL	NULL	2006	10	62792.5455
NULL	NULL	2006	11	213238.0125
NULL	NULL	2006	12	99126.1407
Europe	NULL	NULL	NULL	966221.9606
Europe	NULL	2006	NULL	966221.9606
Europe	NULL	2006	7	109936.0248
Europe	NULL	2006	8	296651.4808
Europe	NULL	2006	9	184477.7563
Europe	NULL	2006	10	62792.5455
Europe	NULL	2006	11	213238.0125
Europe	NULL	2006	12	99126.1407
Europe	FR	NULL	NULL	966221.9606
Europe	FR	2006	NULL	966221.9606
Europe	FR	2006	7	109936.0248
Europe	FR	2006	8	296651.4808
Europe	FR	2006	9	184477.7563
Europe	FR	2006	10	62792.5455
Europe	FR	2006	11	213238.0125
Europe	FR	2006	12	99126.1407

E. 使用 GROUP BY CUBE

在下列範例中，CUBE 運算子傳回的結果集會有一個代表 CUBE 清單中資料行的所有可能組合的群組，以及一個總計群組。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer AS C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY CUBE(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	254013.6014
NULL	NULL	NULL	287	28461.1854
NULL	NULL	NULL	288	17073.0655
NULL	NULL	NULL	290	208479.3505
NULL	NULL	Spa and Exercise Outfitters	NULL	236210.9015
NULL	NULL	Spa and Exercise Outfitters	287	27731.551
NULL	NULL	Spa and Exercise Outfitters	290	208479.3505
NULL	NULL	Versatile Sporting Goods Company	NULL	17802.6999
NULL	NULL	Versatile Sporting Goods Company	287	729.6344
NULL	NULL	Versatile Sporting Goods Company	288	17073.0655
NULL	DE	NULL	NULL	17802.6999
NULL	DE	NULL	287	729.6344
NULL	DE	NULL	288	17073.0655
NULL	DE	Versatile Sporting Goods Company	NULL	17802.6999
NULL	DE	Versatile Sporting Goods Company	287	729.6344
NULL	DE	Versatile Sporting Goods Company	288	17073.0655
NULL	FR	NULL	NULL	236210.9015
NULL	FR	NULL	287	27731.551
NULL	FR	NULL	290	208479.3505
NULL	FR	Spa and Exercise Outfitters	NULL	236210.9015
NULL	FR	Spa and Exercise Outfitters	287	27731.551
NULL	FR	Spa and Exercise Outfitters	290	208479.3505
Europe	NULL	NULL	NULL	254013.6014
Europe	NULL	NULL	287	28461.1854
Europe	NULL	NULL	288	17073.0655
Europe	NULL	NULL	290	208479.3505
Europe	NULL	Spa and Exercise Outfitters	NULL	236210.9015
Europe	NULL	Spa and Exercise Outfitters	287	27731.551
Europe	NULL	Spa and Exercise Outfitters	290	208479.3505
Europe	NULL	Versatile Sporting Goods Company	NULL	17802.6999
Europe	NULL	Versatile Sporting Goods Company	287	729.6344
Europe	NULL	Versatile Sporting Goods Company	288	17073.0655
Europe	DE	NULL	NULL	17802.6999
Europe	DE	NULL	287	729.6344
Europe	DE	NULL	288	17073.0655
Europe	DE	Versatile Sporting Goods Company	NULL	17802.6999
Europe	DE	Versatile Sporting Goods Company	287	729.6344
Europe	DE	Versatile Sporting Goods Company	288	17073.0655
Europe	FR	NULL	NULL	236210.9015
Europe	FR	NULL	287	27731.551
Europe	FR	NULL	290	208479.3505
Europe	FR	Spa and Exercise Outfitters	NULL	236210.9015
Europe	FR	Spa and Exercise Outfitters	287	27731.551
Europe	FR	Spa and Exercise Outfitters	290	208479.3505

F. 搭配複合元素使用 CUBE

在下列範例中，CUBE 運算子傳回的結果集會有一個代表 CUBE 清單中資料行的所有可能組合的群組，以及一個總計群組。

針對已分組的資料行 (T.[Group], T.CountryRegionCode) 和 (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))，運算子將之視為單一資料行來處理。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
GROUP BY CUBE(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

以下為結果集：

Region	Country	Year	Month	Total Sales
NULL	NULL	NULL	NULL	966221.9606
NULL	NULL	2006	7	109936.0248
NULL	NULL	2006	8	296651.4808
NULL	NULL	2006	9	184477.7563
NULL	NULL	2006	10	62792.5455
NULL	NULL	2006	11	213238.0125
NULL	NULL	2006	12	99126.1407
Europe	FR	NULL	NULL	966221.9606
Europe	FR	2006	7	109936.0248
Europe	FR	2006	8	296651.4808
Europe	FR	2006	9	184477.7563
Europe	FR	2006	10	62792.5455
Europe	FR	2006	11	213238.0125
Europe	FR	2006	12	99126.1407

G. 搭配 GROUPING SETS 使用 GROUP BY

在下列範例中，GROUPING SETS 運算子有四個群組，分別代表 SELECT 清單中的每個資料行。運算子在 Region、Country、Store, 和 SalesPersonID 資料行中，針對每個唯一值傳回一個資料列。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityId 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS
    (T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	287	28461.1854
NULL	NULL	NULL	288	17073.0655
NULL	NULL	NULL	290	208479.3505
NULL	NULL	Spa and Exercise Outfitters	NULL	236210.9015
NULL	NULL	Versatile Sporting Goods Company	NULL	17802.6999
NULL	DE	NULL	NULL	17802.6999
NULL	FR	NULL	NULL	236210.9015
Europe	NULL	NULL	NULL	254013.6014

H. 搭配複合元素使用 GROUPING SETS

在下列範例中，GROUPING SETS 清單包含兩個複合元素：(T.[Group], T.CountryRegionCode) 和 (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))。每個複合元素會被視為一個資料行。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

以下為結果集：

Region	Country	Year	Month	Total Sales
NULL	NULL	2006	7	109936.0248
NULL	NULL	2006	8	296651.4808
NULL	NULL	2006	9	184477.7563
NULL	NULL	2006	10	62792.5455
NULL	NULL	2006	11	213238.0125
NULL	NULL	2006	12	99126.1407
Europe	FR	NULL	NULL	966221.9606

I. 搭配多個 GROUPING SETS 使用 GROUP BY

在下列範例中，GROUPING SETS 清單有五個元素。結果集中會有包含下列元素的一個資料列：

• 在 Region 和 Country 資料行中的每個唯一組合值

• 在 Store 資料行中的每個唯一值

• 在 SalesPersonID 和 Region 資料行中的每個唯一組合值

• 在 SalesPersonID 資料行中的每個唯一值

• 總計

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID,T.[Group])
    ,(H.SalesPersonID)
    ,())
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	254013.6014
NULL	NULL	NULL	287	28461.1854
NULL	NULL	NULL	288	17073.0655
NULL	NULL	NULL	290	208479.3505
NULL	NULL	Spa and Exercise Outfitters	NULL	236210.9015
NULL	NULL	Versatile Sporting Goods Company	NULL	17802.6999
Europe	NULL	NULL	287	28461.1854
Europe	NULL	NULL	288	17073.0655
Europe	NULL	NULL	290	208479.3505
Europe	DE	NULL	NULL	17802.6999
Europe	FR	NULL	NULL	236210.9015

J. 搭配部分 GROUP BY 清單中的 ROLLUP 使用 GROUPING SETS

在下列範例中，GROUPING SETS 清單包含 T.[Group] 和 T.CountryRegionCode 資料行的群組，以及 S.Name 和 H.SalesPersonID 資料行的 ROLLUP。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
   ,ROLLUP(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	254013.6014
NULL	NULL	Spa and Exercise Outfitters	NULL	236210.9015
NULL	NULL	Spa and Exercise Outfitters	287	27731.551
NULL	NULL	Spa and Exercise Outfitters	290	208479.3505
NULL	NULL	Versatile Sporting Goods Company	NULL	17802.6999
NULL	NULL	Versatile Sporting Goods Company	287	729.6344
NULL	NULL	Versatile Sporting Goods Company	288	17073.0655
NULL	DE	NULL	NULL	17802.6999
NULL	FR	NULL	NULL	236210.9015
Europe	NULL	NULL	NULL	254013.6014

K. 搭配部分 GROUP BY 清單中的 CUBE 使用 GROUPING SETS

在下列範例中，GROUPING SETS 清單包含 T.[Group] 和 T.CountryRegionCode 資料行的群組，以及 S.Name 和 H.SalesPersonID 資料行的 CUBE。

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
    ,CUBE(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

以下為結果集：

Region	Country	Store	SalesPersonID	Total Sales
NULL	NULL	NULL	NULL	254013.6014
NULL	NULL	NULL	287	28461.1854
NULL	NULL	NULL	288	17073.0655
NULL	NULL	NULL	290	208479.3505
NULL	NULL	Spa and Exercise Outfitters	NULL	236210.9015
NULL	NULL	Spa and Exercise Outfitters	287	27731.551
NULL	NULL	Spa and Exercise Outfitters	290	208479.3505
NULL	NULL	Versatile Sporting Goods Company	NULL	17802.6999
NULL	NULL	Versatile Sporting Goods Company	287	729.6344
NULL	NULL	Versatile Sporting Goods Company	288	17073.0655
NULL	DE	NULL	NULL	17802.6999
NULL	FR	NULL	NULL	236210.9015
Europe	NULL	NULL	NULL	254013.6014

請參閱

參考

GROUP BY (Transact-SQL)

GROUPING (Transact-SQL)

GROUPING_ID (Transact-SQL)

概念

GROUPING SETS 對等項目

GROUP BY 錯誤疑難排解