Artykuł
05/11/2010

GROUPING_ID (Transact-SQL)

Jest to funkcja, która oblicza poziom grupowanie.GROUPING_ID mogą być używane tylko w polu Wybierz <Wybierz opcję> wyświetlić listę, uwzględniając, lub ORDER BY klauzule, gdy określona jest GROUP BY.

Topic link icon Konwencje składni języka Transact-SQL

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

Argumenty

<column_expression>
Czy column_expression w GRUPOWANIE WEDŁUG klauzula.

Zwracany typ

int

Remarks

GROUPING_ID <column_expression> musi pasować do wyrażenie na liście GROUP BY. Na przykład, jeśli są grupowane w parametrze DATEPART (rrrr, <column name>), należy użyć GROUPING_ID (parametrze DATEPART (rrrr, <column name>)); lub jeśli są grupowane według <column name>, użyj GROUPING_ID ()<column name>).

Porównanie (GROUPING_ID) do grupowanie)

GROUPING_ID (<column_expression> [ ,...n ]) dane wejściowe odpowiednik grupowanie (column_expression < >) zwracają dla każdej kolumna na jego liście kolumn w każdym wierszu danych wyjściowych jako ciąg znaków z nich i zer. GROUPING_ID interpretuje tego ciąg jako liczby 2 bazy i zwraca liczbę całkowitą równoważne.Na przykład rozważmy następującą instrukcję: SELECT a, b, c, SUM(d),GROUPING_ID(a,b,c)FROM T GROUP BY <group by list>.The following table shows the GROUPING_ID () input and output values.

Kolumny zagregowane	Dane wejściowe GROUPING_ID (a, b, c) = grupowanie(a) + grupowanie(b) grupowanie(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

Definicja technicznych (GROUPING_ID)

Każdy argument GROUPING_ID musi być elementem na liście GROUP BY.Zwraca wartość (GROUPING_ID) integer Mapa bitowa którego najniższą N bitów może być włączone. Oświetlenie bit Wskazuje odpowiedni argument nie jest grupą kolumna wyjściowy danego wiersza. Najniższa zamówienia bit odpowiada argumentowi N i N-1Ty najniższą kolejność bit odpowiada argument 1.

(GROUPING_ID) inne aktywa

W przypadku kwerendy grupowanie pojedynczych grupowanie)<column_expression>) jest równoważna GROUPING_ID ()<column_expression>), a obie zwracają 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.Za pomocą GROUPING_ID do identyfikowania poziomy grupowanie

The following example returns the count of employees by Name and Title, Name, and company total.GROUPING_ID() is used to create a value for each row in the Title column that identifies its level of aggregation.

USE AdventureWorks;
GO
SELECT D.Name
    ,CASE 
    WHEN GROUPING_ID(D.Name, E.Title) = 0 THEN E.Title
    WHEN GROUPING_ID(D.Name, E.Title) = 1 THEN N'Total: ' + D.Name 
    WHEN GROUPING_ID(D.Name, E.Title) = 3 THEN N'Company Total:'
        ELSE N'Unknown'
    END AS N'Title'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title);

B.Przy użyciu GROUPING_ID do filtrowania wyników zestaw

Prosty przykład

Poniższy kod do zwracania tylko wierszy, które zawierają liczby pracowników według tytułu, usuń znaki komentarza z HAVING GROUPING_ID(D.Name, E.Title); = 0. Aby zwrócić tylko wiersze z liczby pracowników według działów, usuń znaki komentarza z HAVING GROUPING_ID(D.Name, E.Title) = 1;.

USE AdventureWorks;
GO
SELECT D.Name
    ,E.Title
    ,GROUPING_ID(D.Name, E.Title) AS 'Grouping Level'
    ,COUNT(E.EmployeeID) AS N'Employee Count'
FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH
        ON E.EmployeeID = DH.EmployeeID
    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.Title)
--HAVING GROUPING_ID(D.Name, E.Title) = 0; --All titles
--HAVING GROUPING_ID(D.Name, E.Title) = 1; --Group by Name

W tym polu jest zestaw wyników plików niefiltrowanych.

Imię i nazwisko	Title	Poziom grupowanie	Liczba pracowników	Imię i nazwisko
Sterowanie dokumentami	Formant Specialist	0	2	Sterowanie dokumentami
Sterowanie dokumentami	Asystent sterowania dokumentu	0	2	Sterowanie dokumentami
Sterowanie dokumentami	Menedżer sterowania dokumentów	0	1	Sterowanie dokumentami
Sterowanie dokumentami	WARTOŚCI NULL	1	5	Sterowanie dokumentami
Urządzenia i konserwacja	Obiekty administracyjne Asystenta	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	Inspektor konserwacji	0	1	Urządzenia i konserwacja
Urządzenia i konserwacja	WARTOŚCI NULL	1	7	Urządzenia i konserwacja
WARTOŚCI NULL	WARTOŚCI NULL	3	12	WARTOŚCI NULL

Przykład złożony

W poniższym przykładzie GROUPING_ID() Służy do filtrowania zestaw wyników, który zawiera wiele poziomów grupowanie według poziom grupowanie. Podobne kod może być używany do utworzenia widoku, który ma kilka poziomów grupowanie i procedura przechowywana, wywołuje widoku, przekazując parametr, który filtruje widok według poziom grupowanie.

USE AdventureWorks;
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 C.FirstName + ' ' + C.LastName 
        FROM Person.Contact C 
        WHERE C.ContactId = 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 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
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 ROLLUP i moduł do identyfikowania poziomy grupowanie

The code in the following examples show using GROUPING() to compute the Bit Vector(base-2) column.GROUPING_ID() is used to compute the corresponding Integer Equivalent column.Kolejność kolumn w GROUPING_ID() funkcja jest przeciwieństwem kolejność kolumn, kolumn, które są tak łączone przez GROUPING() Funkcja.

W tym przykładzie GROUPING_ID() Służy do tworzenia wartości dla każdego wiersza w Grouping Level Kolumna, aby określić poziom grupowanie. Poziomy grupowanie nie zawsze są listy kolejnych liczb całkowitych, zaczynające się od 1 (0, 1, 2... n).

Uwaga

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

Przykład ROLLUP

W tym przykładzie wszystkie poziomy grupowanie nie są wyświetlane tak samo, jak w poniższym przykładzie moduł.Jeśli kolejność kolumn w ROLLUP Lista zostanie zmieniona, wartości z poziom Grouping Level kolumna będzie miał być zmieniane.

USE AdventureWorks;
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'2003',N'2004')
    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 polu jest zestaw wyników częściowych.

Rok	Miesiąc	Dzień	Całkowita ukończenia	Wektor bitowy (podstawa 2)	Odpowiednik liczby całkowitej	Poziom grupowanie
2003	1	1	1762381	000	0	Rok dzień miesiąca
2003	1	2	21772.35	000	0	Rok dzień miesiąca
2003	2	1	3185233	000	0	Rok dzień miesiąca
2003	2	2	21684.41	000	0	Rok dzień miesiąca
2004	1	1	2239208	000	0	Rok dzień miesiąca
2004	1	2	46458.07	000	0	Rok dzień miesiąca
2004	2	1	3653194	000	0	Rok dzień miesiąca
2004	2	2	54598.55	000	0	Rok dzień miesiąca
2003	1	WARTOŚCI NULL	1784153	100	1	Miesiąc roku
2003	2	WARTOŚCI NULL	3206917	100	1	Miesiąc roku
2004	1	WARTOŚCI NULL	2285666	100	1	Miesiąc roku
2004	2	WARTOŚCI NULL	3707793	100	1	Miesiąc roku
2003	WARTOŚCI NULL	WARTOŚCI NULL	4991070	110	3	Rok
2004	WARTOŚCI NULL	WARTOŚCI NULL	5993459	110	3	Rok
WARTOŚCI NULL	WARTOŚCI NULL	WARTOŚCI NULL	10984529	111	7	Suma końcowa

Przykład moduł

W tym przykładzie GROUPING_ID() funkcja tej używa się do utworzenia wartości dla każdego wiersza w Grouping Level Kolumna, aby określić poziom grupowanie.

W odróżnieniu od ROLLUP w poprzednim przykładzie CUBE Wyświetla wszystkie poziomy grupowanie. Jeśli kolejność kolumn w CUBE Lista zostanie zmieniona, wartości z poziom Grouping Level kolumna będzie miał być zmieniane.

USE AdventureWorks;
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'2003',N'2004')
    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);