Статья
04/01/2012

Использование предложения 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 для работы с составными элементами, показано в примере З далее в этом разделе.

Если в списке оператора GROUPING SETS содержится несколько разделенных запятыми наборов во внутренних скобках, то выходные данные этих наборов объединяются. Результирующий набор является либо перекрестным произведением, либо декартовым произведением группирующим наборов. Как использовать предложение GROUP BY для работы с объединенными операциями ROLLUP, показано в примере Г далее в этом разделе.

Сравнение операторов ROLLUP и CUBE с измерениями OLAP

Некоторые результирующие наборы, сформированные запросами, в которых используются операторы ROLLUP и CUBE, а также некоторые совершаемые ими вычисления совпадают с вычислениями приложений OLAP. Оператор CUBE формирует результирующий набор, который может быть использован для перекрестных отчетов. Операция ROLLUP может вычислить эквивалент измерения или иерархии OLAP.

Например, если существует измерение времени с уровнями или атрибутами года, месяца и дня, то приведенная ниже операция ROLLUP сформирует следующие группирования.

Операция	Группирования
`ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))`	year, month, day year, month year ()

Операция

Группирования

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

Операция

Группирования

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

Операция

Группирование

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. Значение UNKNOWN заменяет значение NULL в тех строках, в которых значения NULL столбца были сгруппированы. Значение ALL заменяет значение NULL для столбца, в котором значение 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 возвращает результирующий набор, который будет сравниваться с результирующими наборами примеров Б — Л. Эти примеры используют операторы 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

Б. Использование оператора 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

В. Использование оператора GROUP BY ROLLUP с обратным порядком столбцов

В следующем примере оператор ROLLUP возвращает результирующий набор, содержащий следующие группирования.

SalesPersonID, Store, Country и Region
SalesPersonID, Store и Country
SalesPersonIDи Store
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 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

Г. Использование предложения 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

Д. Использование оператора 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

Е. Использование оператора 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

Ж. Совместное использование предложения 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

З. Использование оператора 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

И. Совместное использование предложения 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

К. Использование оператора GROUPING SETS с ключевым словом ROLLUP в качестве части списка предложения GROUP BY

В следующем примере в список оператора GROUPING SETS входят группирования для столбцов T.[Group] и T.CountryRegionCode и результат оператора ROLLUP для столбцов S.Name и H.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
   ,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

Л. Использование оператора GROUPING SETS с ключевым словом CUBE в качестве части списка оператора GROUP BY

В следующем примере в список оператора GROUPING SETS входят группирования для столбцов T.[Group] и T.CountryRegionCode и результат оператора CUBE для столбцов S.Name и H.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
    ,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