Użycie klauzuli GROUP BY z operatorami ROLLUP, CUBE i klauzulą GROUPING SETS

Operatory ROLLUP, CUBE i GROUPING SETS są rozszerzeniami klauzuli GROUP BY.Operatory ROLLUP, CUBE lub GROUPING SETS mogą generować taki sam zestaw wyników, jak przy użyciu operatora UNION ALL do łączenia pojedynczego grupowania zapytań, jednak użycie jednego z operatorów klauzuli GROUP BY jest zwykle bardziej skuteczne.

Operator GROUPING SETS może wygenerować ten sam zestaw wyników jak wygenerowany przy użyciu prostych operatorów GROUP BY, ROLLUP lub CUBE.Jeśli nie wszystkie grupowania wygenerowane za pomocą pełnego operatora ROLLUP lub CUBE są wymagane, można użyć klauzuli GROUPING SETS, aby określić tylko żądane grupowania.Lista GROUPING SETS może zawierać zduplikowane grupowania; jeśli operacja GROUPING SETS używana jest z operatorami ROLLUP i CUBE, może generować zduplikowane grupowania.Zduplikowane grupowania są zachowywane, tak jak powinny przy użyciu operacji UNION ALL.

Ostrzeżenie

CUBE, ROLLUP i GROUPING SETS nie obsługują funkcji CHECKSUM_AGG.

Elementy złożone i połączone

Wiele kolumn w nawiasach wewnętrznych na liścieGROUPING SETS traktuje się jako pojedynczy zestaw.Na przykład w klauzuli GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 i Column2 są traktowane jako jedna kolumna.Aby zapoznać się z przykładem sposobu używania operacji GROUPING SETS z elementami złożonymi, zobacz przykład H dalej w tym temacie.

Jeśli operacja GROUPING SETS zawiera wiele zestawów w nawiasach wewnętrznych, oddzielonych przecinkami, dane wyjściowe zestawów są łączone.Zestaw wyników jest iloczynem wektorowym lub iloczynem kartezjańskim zestawów grupowania.Aby zapoznać się z przykładem używania klauzuli GROUP BY za pomocą połączonych operacji ROLLUP, zobacz przykład D dalej w tym temacie.

Operatory ROLLUP i CUBE w porównaniu do wymiarów OLAP

Kwerendy, które używają operatorów ROLLUP i CUBE, generują niektóre z tych samych zestawów wyników i wykonują niektóre takie same obliczenia jak aplikacje OLAP.Operator CUBE generuje zestaw wyników, który może być używany do raportów tabel krzyżowych.Operacja ROLLUP może obliczyć równoważnik wymiaru OLAP lub hierarchii.

Na przykład, biorąc pod uwagę wymiar czas z poziomów lub atrybuty rok, miesiąc i dzień; następująca ROLLUP operacja generuje następujące grupowania.

Operacja	Grupowanie
ROLLUP (DATEPART(yyyy,OrderDate) ,DATEPART(mm,OrderDate) ,DATEPART(dd,OrderDate))	year, month, day year, month year ()

Biorąc pod uwagę wymiar lokalizacja z poziomów region i miasto połączony z poziomem wymiaru czasu rok, miesiąc i dzień; następująca ROLLUP operacja generuje następujące grupowania.

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

A CUBE operacja tych samych poziomów z wymiaru miejsce i czas wyprowadza następujące grupowania.

Operacja

Grupowanie

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 w zestawach wyników

W zestawach wyników generowanych przez grupy operator GROUP BY NULL ma następujące zastosowania:

• Jeśli kolumna grupowania zawiera wartość NULL, wszystkie wartości null są uważane za równe, i są one wprowadzane do jednego grupowania NULL.

• Gdy kolumna jest zagregowana w wierszu, wartość kolumny jest wyświetlana jako NULL.

W poniższym przykładzie użyto funkcji GROUPING, aby pokazać dwa zastosowania wartości NULL.UNKNOWN zastępuje NULL w wierszach, gdzie wartości null w kolumnie zostały pogrupowane.ALL zastępuje NULL w kolumnie, gdzie NULL wskazuje, że kolumna została ujęta w agregacji.

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

Oto zestaw wyników.

Sklep	Rok_sprzedaz	Miesiac_Sprzedaz	Count
Nieznany	Nieznany	Nieznany	1
Nieznany	Nieznany	Styczeń	1
Nieznany	Nieznany	ALL	2
Nieznany	2002	Nieznany	1
Nieznany	2002	ALL	1
Nieznany	ALL	ALL	3
Aktywne Kolarstwo	Nieznany	Nieznany	1
Aktywne Kolarstwo	Nieznany	Styczeń	2
Aktywne Kolarstwo	Nieznany	ALL	3
Aktywne Kolarstwo	2002	Nieznany	1
Aktywne Kolarstwo	2002	ALL	1
Aktywne Kolarstwo	2003	Nieznany	1
Aktywne Kolarstwo	2003	Luty	1
Aktywne Kolarstwo	2003	ALL	2
Aktywne Kolarstwo	ALL	ALL	6
Sklep z rowerami górskimi	Nieznany	Nieznany	1
Sklep z rowerami górskimi	Nieznany	ALL	1
Sklep z rowerami górskimi	2002	Nieznany	1
Sklep z rowerami górskimi	2002	Styczeń	1
Sklep z rowerami górskimi	2002	ALL	2
Sklep z rowerami górskimi	2003	Luty	1
Sklep z rowerami górskimi	2003	Styczeń	1
Sklep z rowerami górskimi	2003	Marzec	1
Sklep z rowerami górskimi	2003	ALL	3
Sklep z rowerami górskimi	ALL	ALL	6
ALL	ALL	ALL	15

PRZYKŁADY

Przykłady podane w tej sekcji używają funkcji agregującej SUM tak, aby zestawy wyników mogły być porównane.Inne funkcje agregujące mogą być także używane do obliczania różnych podsumowań.

A.Używanie prostego operatora GROUP BY

W poniższym przykładzie prosty GROUP BY zwraca zestaw wyników w celu porównania z zestawem wyników z przykładów od B do K.W tych przykładach wykorzystano operatory GROUP BY i tą samą instrukcją 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;

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
Europa	DE	Wszechstronna firma towarów sportowych	284	859.232
Europa	DE	Wszechstronna firma towarów sportowych	289	17691.83
Europa	FR	Dostawcy SPA i ćwiczeń	284	32774.36
Europa	FR	Dostawcy SPA i ćwiczeń	286	246272.4

B.Przy użyciu GROUP BY ROLLUP

W poniższym przykładzie operator ROLLUP zwraca zestaw wyników, który zawiera następujące grupowania:

• Region, Country, Store i SalesPersonID

• Region, Country i Store

• Regionoraz Country

• Region

• suma końcowa

Liczba grupowań generowanych przez ROLLUP jest taka sama jak liczba kolumn na liście ROLLUP oraz suma całkowita grupowania.Liczba wierszy i kolumn w grupowaniach jest określana przez liczbę unikatowych połączeń wartości w kolumnach grupowania.

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;

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
NULL	NULL	NULL	NULL	297597.8
Europa	NULL	NULL	NULL	297597.8
Europa	DE	NULL	NULL	18551.07
Europa	DE	Wszechstronna firma towarów sportowych	NULL	18551.07
Europa	DE	Wszechstronna firma towarów sportowych	284	859.232
Europa	DE	Wszechstronna firma towarów sportowych	289	17691.83
Europa	FR	NULL	NULL	279046.8
Europa	FR	Dostawcy SPA i ćwiczeń	NULL	279046.8
Europa	FR	Dostawcy SPA i ćwiczeń	284	32774.36
Europa	FR	Dostawcy SPA i ćwiczeń	286	246272.4

C.Użycie GROUP BY ROLLUP z odwróconym porządkiem kolumn

W poniższym przykładzie operator ROLLUP zwraca zestaw wyników, który zawiera następujące grupowania:

• SalesPersonID, Store, Country i Region

• SalesPersonID, Store i Country

• SalesPersonIDoraz Store

• SalesPersonID

• suma końcowa

Kolumny na liście ROLLUP są takie same jak w przykładzie B, lecz są one w przeciwnej kolejności.Kolumny są rzutowane od prawej do lewej; kolejność wpływa zatem na grupowanie.Liczba wierszy w zestawie wyników może być zróżnicowana w zależności od porządku kolumn.

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

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
NULL	NULL	NULL	NULL	297597.8
NULL	NULL	NULL	284	33633.59
NULL	NULL	Dostawcy SPA i ćwiczeń	284	32774.36
NULL	FR	Dostawcy SPA i ćwiczeń	284	32774.36
Europa	FR	Dostawcy SPA i ćwiczeń	284	32774.36
NULL	NULL	Wszechstronna firma towarów sportowych	284	859.232
NULL	DE	Wszechstronna firma towarów sportowych	284	859.232
Europa	DE	Wszechstronna firma towarów sportowych	284	859.232
NULL	NULL	NULL	286	246272.4
NULL	NULL	Dostawcy SPA i ćwiczeń	286	246272.4
NULL	FR	Dostawcy SPA i ćwiczeń	286	246272.4
Europa	FR	Dostawcy SPA i ćwiczeń	286	246272.4
NULL	NULL	NULL	289	17691.83
NULL	NULL	Wszechstronna firma towarów sportowych	289	17691.83
NULL	DE	Wszechstronna firma towarów sportowych	289	17691.83
Europa	DE	Wszechstronna firma towarów sportowych	289	17691.83

D.Używanie operatora GROUP BY z połączonymi operacjami ROLLUP

W poniższym przykładzie jest zwracany iloczyn wektorowy dwóch operacji 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);

Oto zestaw wyników.

Region	Country	Rok	Miesiąc	Łączna sprzedaż
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
Europa	NULL	NULL	NULL	966221.9606
Europa	NULL	2006	NULL	966221.9606
Europa	NULL	2006	7	109936.0248
Europa	NULL	2006	8	296651.4808
Europa	NULL	2006	9	184477.7563
Europa	NULL	2006	10	62792.5455
Europa	NULL	2006	11	213238.0125
Europa	NULL	2006	12	99126.1407
Europa	FR	NULL	NULL	966221.9606
Europa	FR	2006	NULL	966221.9606
Europa	FR	2006	7	109936.0248
Europa	FR	2006	8	296651.4808
Europa	FR	2006	9	184477.7563
Europa	FR	2006	10	62792.5455
Europa	FR	2006	11	213238.0125
Europa	FR	2006	12	99126.1407

E.Użycie operatora GROUP BY CUBE

W poniższym przykładzie operator CUBE zwraca zestaw wyników, który ma jedno grupowanie dla wszystkich możliwych kombinacji kolumn na liście CUBE i sumę całkowitą grupowania.

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;

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
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	Dostawcy SPA i ćwiczeń	NULL	236210.9015
NULL	NULL	Dostawcy SPA i ćwiczeń	287	27731.551
NULL	NULL	Dostawcy SPA i ćwiczeń	290	208479.3505
NULL	NULL	Wszechstronna firma towarów sportowych	NULL	17802.6999
NULL	NULL	Wszechstronna firma towarów sportowych	287	729.6344
NULL	NULL	Wszechstronna firma towarów sportowych	288	17073.0655
NULL	DE	NULL	NULL	17802.6999
NULL	DE	NULL	287	729.6344
NULL	DE	NULL	288	17073.0655
NULL	DE	Wszechstronna firma towarów sportowych	NULL	17802.6999
NULL	DE	Wszechstronna firma towarów sportowych	287	729.6344
NULL	DE	Wszechstronna firma towarów sportowych	288	17073.0655
NULL	FR	NULL	NULL	236210.9015
NULL	FR	NULL	287	27731.551
NULL	FR	NULL	290	208479.3505
NULL	FR	Dostawcy SPA i ćwiczeń	NULL	236210.9015
NULL	FR	Dostawcy SPA i ćwiczeń	287	27731.551
NULL	FR	Dostawcy SPA i ćwiczeń	290	208479.3505
Europa	NULL	NULL	NULL	254013.6014
Europa	NULL	NULL	287	28461.1854
Europa	NULL	NULL	288	17073.0655
Europa	NULL	NULL	290	208479.3505
Europa	NULL	Dostawcy SPA i ćwiczeń	NULL	236210.9015
Europa	NULL	Dostawcy SPA i ćwiczeń	287	27731.551
Europa	NULL	Dostawcy SPA i ćwiczeń	290	208479.3505
Europa	NULL	Wszechstronna firma towarów sportowych	NULL	17802.6999
Europa	NULL	Wszechstronna firma towarów sportowych	287	729.6344
Europa	NULL	Wszechstronna firma towarów sportowych	288	17073.0655
Europa	DE	NULL	NULL	17802.6999
Europa	DE	NULL	287	729.6344
Europa	DE	NULL	288	17073.0655
Europa	DE	Wszechstronna firma towarów sportowych	NULL	17802.6999
Europa	DE	Wszechstronna firma towarów sportowych	287	729.6344
Europa	DE	Wszechstronna firma towarów sportowych	288	17073.0655
Europa	FR	NULL	NULL	236210.9015
Europa	FR	NULL	287	27731.551
Europa	FR	NULL	290	208479.3505
Europa	FR	Dostawcy SPA i ćwiczeń	NULL	236210.9015
Europa	FR	Dostawcy SPA i ćwiczeń	287	27731.551
Europa	FR	Dostawcy SPA i ćwiczeń	290	208479.3505

F.Używanie operatora CUBE z elementami złożonymi

W poniższym przykładzie operator CUBE zwraca zestaw wyników, który ma jedno grupowanie dla wszystkich możliwych kombinacji kolumn na liście CUBE i sumę całkowitą grupowania.

Operator przetwarza zgrupowane kolumny (T.[Group], T.CountryRegionCode) i (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) jako pojedyncze kolumny.

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

Oto zestaw wyników.

Region	Country	Rok	Miesiąc	Łączna sprzedaż
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
Europa	FR	NULL	NULL	966221.9606
Europa	FR	2006	7	109936.0248
Europa	FR	2006	8	296651.4808
Europa	FR	2006	9	184477.7563
Europa	FR	2006	10	62792.5455
Europa	FR	2006	11	213238.0125
Europa	FR	2006	12	99126.1407

G.Użycie operatorów GROUP BY z GROUPING SETS

W poniższym przykładzie operator GROUPING SETS posiada cztery grupowania, jedno dla każdej kolumny na liście SELECT.Operator zwraca jeden wiersz dla każdej wartości unikatowej w kolumnach Region, Country, Store, i 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;

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
NULL	NULL	NULL	287	28461.1854
NULL	NULL	NULL	288	17073.0655
NULL	NULL	NULL	290	208479.3505
NULL	NULL	Dostawcy SPA i ćwiczeń	NULL	236210.9015
NULL	NULL	Wszechstronna firma towarów sportowych	NULL	17802.6999
NULL	DE	NULL	NULL	17802.6999
NULL	FR	NULL	NULL	236210.9015
Europa	NULL	NULL	NULL	254013.6014

H.Używanie operacji GROUPING SETS z elementami złożonymi

W poniższym przykładzie lista GROUPING SETS zawiera dwa elementy złożone, (T.[Group], T.CountryRegionCode) i (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)).Każdy element złożony jest traktowany jako jedna kolumna.

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

Oto zestaw wyników.

Region	Country	Rok	Miesiąc	Łączna sprzedaż
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
Europa	FR	NULL	NULL	966221.9606

G.Użycie operatorów GROUP BY z wieloma GROUPING SETS

W poniższym przykładzie lista GROUPING SETS ma pięć elementów.Zestaw wyników zawiera jeden wiersz dla następujących elementów:

• Każda unikatowa kombinacja wartości w kolumnach Region i Country

• Każda unikatowa wartość w kolumnie Store

• Każda unikatowa kombinacja wartości w kolumnach SalesPersonID i Region

• Każda unikatowa wartość w kolumnie SalesPersonID

• Suma końcowa

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;

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
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	Dostawcy SPA i ćwiczeń	NULL	236210.9015
NULL	NULL	Wszechstronna firma towarów sportowych	NULL	17802.6999
Europa	NULL	NULL	287	28461.1854
Europa	NULL	NULL	288	17073.0655
Europa	NULL	NULL	290	208479.3505
Europa	DE	NULL	NULL	17802.6999
Europa	FR	NULL	NULL	236210.9015

J.Użycie GROUPING SETS z operatorem ROLLUP części listy GROUP BY

W poniższym przykładzie lista GROUPING SETS obejmuje grupowania dla kolumn T.[Group] i T.CountryRegionCode i ROLLUP z kolumn S.Name i 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;

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
NULL	NULL	NULL	NULL	254013.6014
NULL	NULL	Dostawcy SPA i ćwiczeń	NULL	236210.9015
NULL	NULL	Dostawcy SPA i ćwiczeń	287	27731.551
NULL	NULL	Dostawcy SPA i ćwiczeń	290	208479.3505
NULL	NULL	Wszechstronna firma towarów sportowych	NULL	17802.6999
NULL	NULL	Wszechstronna firma towarów sportowych	287	729.6344
NULL	NULL	Wszechstronna firma towarów sportowych	288	17073.0655
NULL	DE	NULL	NULL	17802.6999
NULL	FR	NULL	NULL	236210.9015
Europa	NULL	NULL	NULL	254013.6014

K.Użycie GROUPING SETS z operatorem CUBE części listy GROUP BY

W poniższym przykładzie lista GROUPING SETS obejmuje grupowania dla kolumn T.[Group] i T.CountryRegionCode i CUBE z kolumn S.Name i 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;

Oto zestaw wyników.

Region	Country	Sklep	ID_sprzedawcy	Łączna sprzedaż
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	Dostawcy SPA i ćwiczeń	NULL	236210.9015
NULL	NULL	Dostawcy SPA i ćwiczeń	287	27731.551
NULL	NULL	Dostawcy SPA i ćwiczeń	290	208479.3505
NULL	NULL	Wszechstronna firma towarów sportowych	NULL	17802.6999
NULL	NULL	Wszechstronna firma towarów sportowych	287	729.6344
NULL	NULL	Wszechstronna firma towarów sportowych	288	17073.0655
NULL	DE	NULL	NULL	17802.6999
NULL	FR	NULL	NULL	236210.9015
Europa	NULL	NULL	NULL	254013.6014

Zobacz także

Odwołanie

Grupowanie według języka Transact-SQL ()

Grupowanie (Transact-SQL)

GROUPING_ID (Transact-SQL)

Koncepcje

Grupowanie zestawów odpowiedniki

Rozwiązywanie problemów z błędami według grupy