Artykuł
07/26/2011

GROUPING_ID (Transact-SQL)

Jest to funkcja oblicza poziom grupowanie.GROUPING_ID można używać tylko w polu Wybierz <zaznacz> listy POSIADAJĄCE, lub ORDER BY klauzule, gdy jest określony przez GRUPĘ.

Ikona łącza do tematu Języka Transact-SQL składni konwencje

Składnia

GROUPING_ID ( <column_expression>[ ,...n ] )

Argumenty

<column_expression>
Jest column_expression w GROUP BY klauzula.

Zwracany typ

int

Uwagi

GROUPING_ID <column_expression> musi dokładnie odpowiadać wyrażenie na liście GROUP BY.Na przykład, jeśli grupowanie według DATEPART (rrrr, <column name>), użyj GROUPING_ID (DATEPART (rrrr, <column name>)); lub w przypadku grupowanie według <column name>, użyj GROUPING_ID (<column name>).

Porównanie (GROUPING_ID) do GRUPOWANIA)

GROUPING_ID (<column_expression> [ ,...n ]) zrzutów równoważne GRUPOWANIA (<column_expression>) zwrotu dla każdej kolumna w jego liście kolumna w każdym wierszu danych wyjściowych jako ciąg jedynek i zer.GROUPING_ID interpretuje ciąg jako numer base 2 i zwraca liczbę całkowitą równoważne.Na przykład rozważmy następującą instrukcja: SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>.W poniższej tabela przedstawiono (GROUPING_ID) wartości wejściowych i wyjściowych.

Zagregowane kolumn	GROUPING_ID (a, b, c) wejściowe = GROUPING(a) + GROUPING(b) + GROUPING(c)	Dane wyjściowe () GROUPING_ID
a	100	4
b	010	2
c	001	1
ab	110	6
ac	101	5
bc	011	3
abc	111	7

Techniczne definicji (GROUPING_ID)

Każdy argument GROUPING_ID musi być elementem listy Grupuj według.Zwraca (GROUPING_ID) integer mapy bitowej, której najniższy n bitów może być lit.Świeci bit wskazuje, że odpowiednie argument jest nie grupowanie kolumna wyjściowych danego wiersza.Najniższą kolejność bit odpowiada argument n i N-1cz najniższą kolejność bit odpowiada argumentowi 1.

(GROUPING_ID) odpowiedników

Dla kwerendy z pojedynczym grupowanie GRUPOWANIA (<column_expression>) jest równoważna GROUPING_ID (<column_expression>), i obie zwracają wartość 0.

Na przykład poniższe instrukcje są równoważne:

SELECT GROUPING_ID(A,B)
FROM T 
GROUP BY CUBE(A,B)

SELECT 3 FROM T GROUP BY ()
UNION ALL
SELECT 1 FROM T GROUP BY A
UNION ALL
SELECT 2 FROM T GROUP BY B
UNION ALL
SELECT 0 FROM T GROUP BY A,B

Przykłady

A.Określenie poziomów grupowanie za pomocą GROUPING_ID

Poniższy przykład zwraca liczbę pracowników przez Name i Title, Name, i Suma firmy.GROUPING_ID()Służy do tworzenia wartości dla każdego wiersza w Title kolumna , która identyfikuje poziom agregacja.

USE AdventureWorks2008R2;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 0 THEN E.JobTitle
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.JobTitle) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Job Title'
    ,COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.BusinessEntityID = DH.BusinessEntityID
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.JobTitle);

B.Używanie GROUPING_ID do filtrowania, zestaw wyników

Prosty przykład

W poniższym kodzie zwraca tylko wiersze, których liczba pracowników według tytułu, usuń znaki komentarza z HAVING GROUPING_ID(D.Name, E.JobTitle); = 0.Aby przywrócić tylko wiersze z liczba pracowników według działów, usuń znaki komentarza z HAVING GROUPING_ID(D.Name, E.JobTitle) = 1;.

USE AdventureWorks2008R2;
GO
SELECT D.Name
    ,E.JobTitle
    ,GROUPING_ID(D.Name, E.JobTitle) AS 'Grouping Level'
    ,COUNT(E.BusinessEntityID) AS N'Employee Count'
FROM HumanResources.Employee AS E
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH
        ON E.BusinessEntityID = DH.BusinessEntityID
    INNER JOIN HumanResources.Department AS D
        ON D.DepartmentID = DH.DepartmentID     
WHERE DH.EndDate IS NULL
    AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.JobTitle)
--HAVING GROUPING_ID(D.Name, E.JobTitle) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.JobTitle) = 1; --Group by Name;

Oto niefiltrowanych zestaw wyników.

Nazwa	Tytuł	Poziom grupowania	Liczba pracowników	Nazwa
Kontroli dokumentów	Kontrola specjalistyczne	0	2	Kontroli dokumentów
Kontroli dokumentów	Dokument kontroli Asystenta	0	2	Kontroli dokumentów
Kontroli dokumentów	Menedżer sterowania dokumentu	0	1	Kontroli dokumentów
Kontroli dokumentów	NULL	1	5	Kontroli dokumentów
Urządzenia i konserwacja	Urządzenia do Asystenta administracyjnego	0	1	Urządzenia i konserwacja
Urządzenia i konserwacja	Menedżer urządzeń	0	1	Urządzenia i konserwacja
Urządzenia i konserwacja	Janitor	0	4	Urządzenia i konserwacja
Urządzenia i konserwacja	Kierownik obsługi	0	1	Urządzenia i konserwacja
Urządzenia i konserwacja	NULL	1	7	Urządzenia i konserwacja
NULL	NULL	3	12	NULL

Przykład złożony

W poniższym przykładzie GROUPING_ID() jest używane do filtrowania, zestaw wyników , który zawiera wiele poziomów grupowaniepoziom grupowanie . Podobne kod może służyć do utworzenia widoku, który ma kilka poziomów grupowanie i wywołuje widoku przekazanie parametru filtruje widok procedura składowana grupowanie poziom.

USE AdventureWorks2008R2;
GO
DECLARE @Grouping nvarchar(50);
DECLARE @GroupingLevel smallint;
SET @Grouping = N'CountryRegionCode Total';

SELECT @GroupingLevel = (
    CASE @Grouping
        WHEN N'Grand Total'             THEN 15
        WHEN N'SalesPerson Total'       THEN 14
        WHEN N'Store Total'             THEN 13
        WHEN N'Store SalesPerson Total' THEN 12
        WHEN N'CountryRegionCode Total' THEN 11
        WHEN N'Group Total'             THEN 7
        ELSE N'Unknown'
    END);

SELECT 
    T.[Group]
    ,T.CountryRegionCode
    ,S.Name AS N'Store'
    ,(SELECT P.FirstName + ' ' + P.LastName 
        FROM Person.Person AS P 
        WHERE P.BusinessEntityID = H.SalesPersonID)
        AS N'Sales Person'
    ,SUM(TotalDue)AS N'TotalSold'
    ,CAST(GROUPING(T.[Group])AS char(1)) + 
        CAST(GROUPING(T.CountryRegionCode)AS char(1)) + 
        CAST(GROUPING(S.Name)AS char(1)) + 
        CAST(GROUPING(H.SalesPersonID)AS char(1)) 
        AS N'GROUPING base-2'
    ,GROUPING_ID((T.[Group])
        ,(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
        ) AS N'GROUPING_ID'
    ,CASE 
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 15 THEN N'Grand Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 14 THEN N'SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 13 THEN N'Store Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 12 THEN N'Store SalesPerson Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) = 11 THEN N'CountryRegionCode Total'
        WHEN GROUPING_ID(
            (T.[Group]),(T.CountryRegionCode)
            ,(S.Name),(H.SalesPersonID)
            ) =  7 THEN N'Group Total'
        ELSE N'Error'
        END AS N'Level'
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
GROUP BY GROUPING SETS ((S.Name,H.SalesPersonID)
    ,(H.SalesPersonID),(S.Name)
    ,(T.[Group]),(T.CountryRegionCode),()
    )
HAVING GROUPING_ID(
    (T.[Group]),(T.CountryRegionCode),(S.Name),(H.SalesPersonID)
    ) = @GroupingLevel
ORDER BY 
    GROUPING_ID(S.Name,H.SalesPersonID),GROUPING_ID((T.[Group])
    ,(T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID))ASC;

C.Za pomocą (GROUPING_ID) z pakietu ZBIORCZEGO i modułu do identyfikacji poziomów grupowanie

Pokaż kod w poniższych przykładach za pomocą GROUPING() do obliczenia Bit Vector(base-2) kolumna.GROUPING_ID()jest używana do obliczania odpowiednich Integer Equivalent kolumna.Kolejność kolumna w GROUPING_ID() funkcja jest przeciwieństwem kolejność kolumna kolumn, które są tak łączone, przez GROUPING() funkcja.

W tych przykładach GROUPING_ID() jest używany do tworzenia wartości dla każdego wiersza w Grouping Level kolumna , aby zidentyfikować poziom grupowanie.Poziomy grupowania nie zawsze są listy kolejnych liczb całkowitych, które rozpoczynają się od 1 (0, 1, 2...n).

Ostrzeżenie

Grupowanie i GROUPING_ID mogą być używane n klauzula HAVING do filtrowania, zestaw wyników.

Przykład pakiet zbiorczy aktualizacji

W tym przykładzie wszystkie poziomy grupowanie nie wyglądają tak samo, jak w poniższym przykładzie modułu.Jeśli kolejność kolumn w ROLLUP listy zostanie zmieniony, wartości poziom w Grouping Level kolumna zostanie również muszą zostać zmienione.

USE AdventureWorks2008R2;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
     AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'not used'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2007',N'2008')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY ROLLUP(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(mm,OrderDate)
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

W tym miejscu jest częściowe zestaw wyników.

Rok	Miesiąc	Dzień	Suma należna	Wektor bitowy (base-2)	Równoważna liczba całkowita	Poziom grupowania
2007	1	1	1497452.6066	000	0	Rok miesiąc dzień
2007	1	2	21772.3494	000	0	Rok miesiąc dzień
2007	2	1	2705653.5913	000	0	Rok miesiąc dzień
2007	2	2	21684.4068	000	0	Rok miesiąc dzień
2008	1	1	1908122.0967	000	0	Rok miesiąc dzień
2008	1	2	46458.0691	000	0	Rok miesiąc dzień
2008	2	1	3108771.9729	000	0	Rok miesiąc dzień
2008	2	2	54598.5488	000	0	Rok miesiąc dzień
2007	1	NULL	1519224.956	100	1	Miesiąc roku
2007	2	NULL	2727337.9981	100	1	Miesiąc roku
2008	1	NULL	1954580.1658	100	1	Miesiąc roku
2008	2	NULL	3163370.5217	100	1	Miesiąc roku
2007	NULL	NULL	4246562.9541	110	3	Rok
2008	NULL	NULL	5117950.6875	110	3	Rok
NULL	NULL	NULL	9364513.6416	111	7	Suma końcowa

Przykład modułu

W tym przykładzie GROUPING_ID() funkcja jest używana do tworzenia wartości dla każdego wiersza w Grouping Level kolumna , aby zidentyfikować poziom grupowanie.

W przeciwieństwie do ROLLUP w poprzednim przykładzie CUBE kieruje do wyjścia wszystkie grupowanie poziomów.Jeśli kolejność kolumn w CUBE listy zostanie zmieniony, wartości poziom w Grouping Level kolumna zostanie również muszą zostać zmienione.

USE AdventureWorks2008R2;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,DATEPART(mm,OrderDate) AS N'Month'
    ,DATEPART(dd,OrderDate) AS N'Day'
    ,SUM(TotalDue) AS N'Total Due'
    ,CAST(GROUPING(DATEPART(dd,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(mm,OrderDate))AS char(1)) + 
        CAST(GROUPING(DATEPART(yyyy,OrderDate))AS char(1)) 
        AS N'Bit Vector(base-2)'
    ,GROUPING_ID(DATEPART(yyyy,OrderDate)
        ,DATEPART(mm,OrderDate)
        ,DATEPART(dd,OrderDate)) 
        AS N'Integer Equivalent'
    ,CASE
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate)
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 0 THEN N'Year Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 1 THEN N'Year Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 2 THEN N'Year Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 3 THEN N'Year'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 4 THEN N'Month Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 5 THEN N'Month'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 6 THEN N'Day'
        WHEN GROUPING_ID(DATEPART(yyyy,OrderDate) 
            ,DATEPART(mm,OrderDate),DATEPART(dd,OrderDate)
            ) = 7 THEN N'Grand Total'
    ELSE N'Error'
    END AS N'Grouping Level'
FROM Sales.SalesOrderHeader
WHERE DATEPART(yyyy,OrderDate) IN(N'2007',N'2008')
    AND DATEPART(mm,OrderDate) IN(1,2)
    AND DATEPART(dd,OrderDate) IN(1,2)
GROUP BY CUBE(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))
ORDER BY GROUPING_ID(DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate)
    )
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate);

W tym miejscu jest częściowe zestaw wyników.

Rok	Miesiąc	Dzień	Suma należna	Wektor bitowy (base-2)	Równoważna liczba całkowita	Poziom grupowania
2007	1	1	1497452.6066	000	0	Rok miesiąc dzień
2007	1	2	21772.3494	000	0	Rok miesiąc dzień
2007	2	1	2705653.5913	000	0	Rok miesiąc dzień
2007	2	2	21684.4068	000	0	Rok miesiąc dzień
2008	1	1	1908122.0967	000	0	Rok miesiąc dzień
2008	1	2	46458.0691	000	0	Rok miesiąc dzień
2008	2	1	3108771.9729	000	0	Rok miesiąc dzień
2008	2	2	54598.5488	000	0	Rok miesiąc dzień
2007	1	NULL	1519224.956	100	1	Miesiąc roku
2007	2	NULL	2727337.9981	100	1	Miesiąc roku
2008	1	NULL	1954580.1658	100	1	Miesiąc roku
2008	2	NULL	3163370.5217	100	1	Miesiąc roku
2007	NULL	1	4203106.1979	010	2	Dzień roku
2007	NULL	2	43456.7562	010	2	Dzień roku
2008	NULL	1	5016894.0696	010	2	Dzień roku
2008	NULL	2	101056.6179	010	2	Dzień roku
2007	NULL	NULL	4246562.9541	110	3	Rok
2008	NULL	NULL	5117950.6875	110	3	Rok
NULL	1	1	3405574.7033	001	4	Dzień miesiąca
NULL	1	2	68230.4185	001	4	Dzień miesiąca
NULL	2	1	5814425.5642	001	4	Dzień miesiąca
NULL	2	2	76282.9556	001	4	Dzień miesiąca
NULL	1	NULL	3473805.1218	101	5	Miesiąc
NULL	2	NULL	5890708.5198	101	5	Miesiąc
NULL	NULL	1	9220000.2675	011	6	Dzień
NULL	NULL	2	144513.3741	011	6	Dzień
NULL	NULL	NULL	9364513.6416	111	7	Suma końcowa