共用方式為


搭配 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

Febuary

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

Febuary

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 AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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 AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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 AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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 AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
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

3031201

NULL

NULL

2004

NULL

3031201

NULL

NULL

2004

1

208553.6

NULL

NULL

2004

2

819466.6

NULL

NULL

2004

3

298579.1

NULL

NULL

2004

4

294427.7

NULL

NULL

2004

5

1070679

NULL

NULL

2004

6

339495.1

Europe

NULL

NULL

NULL

3031201

Europe

NULL

2004

NULL

3031201

Europe

NULL

2004

1

208553.6

Europe

NULL

2004

2

819466.6

Europe

NULL

2004

3

298579.1

Europe

NULL

2004

4

294427.7

Europe

NULL

2004

5

1070679

Europe

NULL

2004

6

339495.1

Europe

DE

NULL

NULL

1196260

Europe

DE

2004

NULL

1196260

Europe

DE

2004

1

155066.2

Europe

DE

2004

2

197801.8

Europe

DE

2004

3

180977.7

Europe

DE

2004

4

222683.4

Europe

DE

2004

5

258962

Europe

DE

2004

6

180769.1

Europe

FR

NULL

NULL

1834941

Europe

FR

2004

NULL

1834941

Europe

FR

2004

1

53487.37

Europe

FR

2004

2

621664.9

Europe

FR

2004

3

117601.4

Europe

FR

2004

4

71744.28

Europe

FR

2004

5

811716.9

Europe

FR

2004

6

158726

E. 使用 GROUP BY CUBE

在下列範例中,CUBE 運算子傳回之結果集的一個群組,會是 CUBE 清單與總計群組中資料行的所有可能組合。

USE AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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

297597.8

NULL

NULL

NULL

284

33633.59

NULL

NULL

NULL

286

246272.4

NULL

NULL

NULL

289

17691.83

NULL

NULL

Spa and Exercise Outfitters

NULL

279046.8

NULL

NULL

Spa and Exercise Outfitters

284

32774.36

NULL

NULL

Spa and Exercise Outfitters

286

246272.4

NULL

NULL

Versatile Sporting Goods Company

NULL

18551.07

NULL

NULL

Versatile Sporting Goods Company

284

859.232

NULL

NULL

Versatile Sporting Goods Company

289

17691.83

NULL

DE

NULL

NULL

18551.07

NULL

DE

NULL

284

859.232

NULL

DE

NULL

289

17691.83

NULL

DE

Versatile Sporting Goods Company

NULL

18551.07

NULL

DE

Versatile Sporting Goods Company

284

859.232

NULL

DE

Versatile Sporting Goods Company

289

17691.83

NULL

FR

NULL

NULL

279046.8

NULL

FR

NULL

284

32774.36

NULL

FR

NULL

286

246272.4

NULL

FR

Spa and Exercise Outfitters

NULL

279046.8

NULL

FR

Spa and Exercise Outfitters

284

32774.36

NULL

FR

Spa and Exercise Outfitters

286

246272.4

Europe

NULL

NULL

NULL

297597.8

Europe

NULL

NULL

284

33633.59

Europe

NULL

NULL

286

246272.4

Europe

NULL

NULL

289

17691.83

Europe

NULL

Spa and Exercise Outfitters

NULL

279046.8

Europe

NULL

Spa and Exercise Outfitters

284

32774.36

Europe

NULL

Spa and Exercise Outfitters

286

246272.4

Europe

NULL

Versatile Sporting Goods Company

NULL

18551.07

Europe

NULL

Versatile Sporting Goods Company

284

859.232

Europe

NULL

Versatile Sporting Goods Company

289

17691.83

Europe

DE

NULL

NULL

18551.07

Europe

DE

NULL

284

859.232

Europe

DE

NULL

289

17691.83

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

NULL

284

32774.36

Europe

FR

NULL

286

246272.4

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

F. 搭配複合元素使用 CUBE

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

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

USE AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
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

3031201

NULL

NULL

2004

1

208553.6

NULL

NULL

2004

2

819466.6

NULL

NULL

2004

3

298579.1

NULL

NULL

2004

4

294427.7

NULL

NULL

2004

5

1070679

NULL

NULL

2004

6

339495.1

Europe

DE

NULL

NULL

1196260

Europe

DE

2004

1

155066.2

Europe

DE

2004

2

197801.8

Europe

DE

2004

3

180977.7

Europe

DE

2004

4

222683.4

Europe

DE

2004

5

258962

Europe

DE

2004

6

180769.1

Europe

FR

NULL

NULL

1834941

Europe

FR

2004

1

53487.37

Europe

FR

2004

2

621664.9

Europe

FR

2004

3

117601.4

Europe

FR

2004

4

71744.28

Europe

FR

2004

5

811716.9

Europe

FR

2004

6

158726

G. 搭配 GROUPING SETS 使用 GROUP BY

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

USE AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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

284

33633.59

NULL

NULL

NULL

286

246272.4

NULL

NULL

NULL

289

17691.83

NULL

NULL

Spa and Exercise Outfitters

NULL

279046.8

NULL

NULL

Versatile Sporting Goods Company

NULL

18551.07

NULL

DE

NULL

NULL

18551.07

NULL

FR

NULL

NULL

279046.8

Europe

NULL

NULL

NULL

297597.8

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

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

USE AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2004'
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

2004

1

208553.6

NULL

NULL

2004

2

819466.6

NULL

NULL

2004

3

298579.1

NULL

NULL

2004

4

294427.7

NULL

NULL

2004

5

1070679

NULL

NULL

2004

6

339495.1

Europe

DE

NULL

NULL

1196260

Europe

FR

NULL

NULL

1834941

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

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

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

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

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

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

  • 總計

USE AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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

297597.8

NULL

NULL

NULL

284

33633.59

NULL

NULL

NULL

286

246272.4

NULL

NULL

NULL

289

17691.83

NULL

NULL

Spa and Exercise Outfitters

NULL

279046.8

NULL

NULL

Versatile Sporting Goods Company

NULL

18551.07

Europe

NULL

NULL

284

33633.59

Europe

NULL

NULL

286

246272.4

Europe

NULL

NULL

289

17691.83

Europe

DE

NULL

NULL

18551.07

Europe

FR

NULL

NULL

279046.8

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

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

USE AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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

297597.8

NULL

NULL

Spa and Exercise Outfitters

NULL

279046.8

NULL

NULL

Spa and Exercise Outfitters

284

32774.36

NULL

NULL

Spa and Exercise Outfitters

286

246272.4

NULL

NULL

Versatile Sporting Goods Company

NULL

18551.07

NULL

NULL

Versatile Sporting Goods Company

284

859.232

NULL

NULL

Versatile Sporting Goods Company

289

17691.83

NULL

DE

NULL

NULL

18551.07

NULL

FR

NULL

NULL

279046.8

Europe

NULL

NULL

NULL

297597.8

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

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

USE AdventureWorks;
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.CustomerID  = S.CustomerID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON S.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(284, 286, 289)
    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

297597.8

NULL

NULL

NULL

284

33633.59

NULL

NULL

NULL

286

246272.4

NULL

NULL

NULL

289

17691.83

NULL

NULL

Spa and Exercise Outfitters

NULL

279046.8

NULL

NULL

Spa and Exercise Outfitters

284

32774.36

NULL

NULL

Spa and Exercise Outfitters

286

246272.4

NULL

NULL

Versatile Sporting Goods Company

NULL

18551.07

NULL

NULL

Versatile Sporting Goods Company

284

859.232

NULL

NULL

Versatile Sporting Goods Company

289

17691.83

NULL

DE

NULL

NULL

18551.07

NULL

FR

NULL

NULL

279046.8

Europe

NULL

NULL

NULL

297597.8