将 GROUP BY 与 ROLLUP、CUBE 和 GROUPING SETS 一起使用

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 列表在内部括号中包含由逗号分隔的多个集时，集的输出将串联在一起。结果集是分组集的叉积或笛卡尔积。有关如何将 GROUP BY 与串联 ROLLUP 操作一起使用的示例，请参阅本主题后面的示例 D。

ROLLUP 和 CUBE 与 OLAP 维度的比较

使用 ROLLUP 和 CUBE 运算符的查询会生成某些与 OLAP 应用程序生成的结果集相同的结果集，并会执行某些与 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。

下例使用 GROUPING 函数演示 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 的结果集进行比较。这些示例使用 GROUP BY 运算符和相同的 SELECT 语句。

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. 将 GROUP BY 与串联 ROLLUP 操作一起使用

下例将返回两个 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. 将 GROUP BY 与 GROUPING SETS 一起使用

在下例中，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. 将 GROUP BY 与多个 GROUPING SETS 一起使用

在下例中，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. 使用 GROUPING SETS 与部分 GROUP BY 列表的 ROLLUP

在下例中，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. 使用 GROUPING SETS 与部分 GROUP BY 列表的 CUBE

在下例中，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 错误故障排除