现在的位置: 首页 > 综合 > 正文

SQL学习笔记：将 GROUP BY 与 ROLLUP、CUBE 和 GROUPING SETS 一起使用

2013年02月03日 ⁄ 综合 ⁄ 共 18133字 ⁄ 字号小中大 ⁄ 评论关闭

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') INSERT INTO dbo.GroupingNULLS VALUES( NULL,'2002',NULL) INSERT INTO dbo.GroupingNULLS VALUES( NULL,NULL,NULL) INSERT INTO dbo.GroupingNULLS VALUES( 'Active Cycling',NULL ,'January'); INSERT INTO dbo.GroupingNULLS VALUES( 'Active Cycling','2002',NULL); INSERT INTO dbo.GroupingNULLS VALUES( 'Active Cycling',NULL ,NULL;) INSERT INTO dbo.GroupingNULLS VALUES( 'Active Cycling',NULL,'January'); INSERT INTO dbo.GroupingNULLS VALUES( 'Active Cycling','2003','Febuary'); INSERT INTO dbo.GroupingNULLS VALUES( 'Active Cycling','2003',NULL); INSERT INTO dbo.GroupingNULLS VALUES( 'Mountain Bike Store','2002','January'); INSERT INTO dbo.GroupingNULLS VALUES( 'Mountain Bike Store','2002',NULL); INSERT INTO dbo.GroupingNULLS VALUES( 'Mountain Bike Store',NULL,NULL); INSERT INTO dbo.GroupingNULLS VALUES( 'Mountain Bike Store','2003','January'); INSERT INTO dbo.GroupingNULLS VALUES( 'Mountain Bike Store','2003','Febuary'); INSERT INTO dbo.GroupingNULLS VALUES( '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);

USE tempdb;
GO
CREATE TABLE dbo.GroupingNULLS (
    Store nvarchar(19)
    ,SaleYear nvarchar(4)
    ,SaleMonth nvarchar (7))
INSERT INTO dbo.GroupingNULLS VALUES(
NULL,NULL,'January')
INSERT INTO dbo.GroupingNULLS VALUES(
NULL,'2002',NULL)
INSERT INTO dbo.GroupingNULLS VALUES(
NULL,NULL,NULL)
INSERT INTO dbo.GroupingNULLS VALUES(
'Active Cycling',NULL ,'January');
INSERT INTO dbo.GroupingNULLS VALUES(
'Active Cycling','2002',NULL);
INSERT INTO dbo.GroupingNULLS VALUES(
'Active Cycling',NULL ,NULL;)
INSERT INTO dbo.GroupingNULLS VALUES(
'Active Cycling',NULL,'January');
INSERT INTO dbo.GroupingNULLS VALUES(
'Active Cycling','2003','Febuary');
INSERT INTO dbo.GroupingNULLS VALUES(
'Active Cycling','2003',NULL);
INSERT INTO dbo.GroupingNULLS VALUES(
'Mountain Bike Store','2002','January');
INSERT INTO dbo.GroupingNULLS VALUES(
'Mountain Bike Store','2002',NULL);
INSERT INTO dbo.GroupingNULLS VALUES(
'Mountain Bike Store',NULL,NULL);
INSERT INTO dbo.GroupingNULLS VALUES(
'Mountain Bike Store','2003','January');
INSERT INTO dbo.GroupingNULLS VALUES(
'Mountain Bike Store','2003','Febuary');
INSERT INTO dbo.GroupingNULLS VALUES(
'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 的结果集进行比较。这些示例使用 GROUP BY 运算符和相同的 SELECT 语句。


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;

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;

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];

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

下例将返回两个 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);

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;

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);

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. 将 GROUP BY 与 GROUPING SETS 一起使用

在下例中，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;

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);

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

在下例中，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;

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

在下例中，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;

复制代码

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

在下例中，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;

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

【上篇】Word弹出“无法访问您试图使用功能所在的网络位置”
【下篇】【比大小 73】

作者: ktlxyx

该日志由 ktlxyx 于11年前发表在综合分类下，最后更新于 2013年02月03日.
转载请注明: SQL学习笔记：将 GROUP BY 与 ROLLUP、CUBE 和 GROUPING SETS 一起使用 | 学步园 +复制链接

抱歉!评论已关闭.

学步园

SQL学习笔记：将 GROUP BY 与 ROLLUP、CUBE 和 GROUPING SETS 一起使用

组合元素和串联元素

ROLLUP 和 CUBE 与 OLAP 维度的比较

结果集中的 NULL

示例

A. 使用简单 GROUP BY

B. 使用 GROUP BY ROLLUP

C. 在列顺序颠倒的情况下使用 GROUP BY ROLLUP

D. 将 GROUP BY 与串联 ROLLUP 操作一起使用

E. 使用 GROUP BY CUBE

F. 使用 CUBE 与组合元素

G. 将 GROUP BY 与 GROUPING SETS 一起使用

H. 使用 GROUPING SETS 与组合元素

I. 将 GROUP BY 与多个 GROUPING SETS 一起使用

J. 使用 GROUPING SETS 与部分 GROUP BY 列表的 ROLLUP

K. 使用 GROUPING SETS 与部分 GROUP BY 列表的 CUBE

作者: ktlxyx

书签

最新文章New

本站推荐

返回首页