Nuta
Dostęp do tej strony wymaga autoryzacji. Możesz spróbować zalogować się lub zmienić katalogi.
Dostęp do tej strony wymaga autoryzacji. Możesz spróbować zmienić katalogi.
Dotyczy:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBaza danych SQL w usłudze Microsoft Fabric
GROUPING_ID Jest funkcją obliczającą poziom grupowania.
GROUPING_IDmoże być używany tylko w liścieSELECT <select>, , lub HAVING klauzulach, ORDER BYgdy GROUP BY jest to określone.
Transact-SQL konwencje składni
Składnia
GROUPING_ID ( <column_expression> [ , ...n ] )
Arguments
<column_expression>
column_expression w klauzuli SELECT - GROUP BY .
Typy zwracane
int
Uwagi
Musi dokładnie odpowiadać GROUPING_ID <column_expression> wyrażeniu z listy GROUP BY . Na przykład, jeśli grupujesz przez DATEPART (yyyy, <column name>), użyj GROUPING_ID (DATEPART (yyyy, <column name>)); lub jeśli grupujesz przez <column name>, użyj GROUPING_ID (<column name>).
Porównaj GROUPING_ID() z GROUPING()
GROUPING_ID (<column_expression> [ , ...n ]) wprowadza równowartość zwrotu GROUPING (<column_expression>) dla każdej kolumny w swojej liście kolumn w każdym wierszu wyjściowym, jako ciąg jedynek i zer.
GROUPING_ID interpretuje ten ciąg jako liczbę z bazy 2 i zwraca równoważną liczbę całkowitą.
Na przykład rozważmy następujące stwierdzenie:
SELECT a, b, c, SUM(d),
GROUPING_ID(a, b, c)
FROM T
GROUP BY <group_by_list>
Ta tabela pokazuje wartości wejściowe i wyjściowe GROUPING_ID() .
| Kolumny zagregowane | GROUPING_ID (a, b, c) wejście = GRUPOWANIE(a) + GRUPOWANIE(b) + GRUPOWANIE(c) | GROUPING_ID() wyjście |
|---|---|---|
a |
100 |
4 |
b |
010 |
2 |
c |
001 |
1 |
ab |
110 |
6 |
ac |
101 |
5 |
bc |
011 |
3 |
abc |
111 |
7 |
Definicja techniczna GROUPING_ID()
Każdy GROUPING_ID argument musi być elementem listy GROUP BY .
GROUPING_ID() zwraca całkowitą bitmapę, której najniższe n bitów może być podświetlone. Oświetlony bit oznacza, że odpowiadający argument nie jest kolumną grupowania dla danego wiersza wyjściowego. Bit najniższego rzędu odpowiada argumentowi n, a n-1 bit najniższego rzędu argumentowi 1.
GROUPING_ID() odpowiedników
Dla pojedynczego zapytania grupującego jest GROUPING (<column_expression>) równoważne GROUPING_ID (<column_expression>), a oba zwracają 0.
Na przykład następujące stwierdzenia są równoważne:
Oświadczenie A
SELECT GROUPING_ID(A, B)
FROM T
GROUP BY CUBE(A, B)
Oświadczenie 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
Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2025 lub AdventureWorksDW2025, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.
A. Użyj GROUPING_ID do identyfikacji poziomów grupowania
Poniższy przykład zwraca liczbę pracowników przez Name i Title, oraz sumę firm w bazie AdventureWorks2025 danych.
GROUPING_ID() jest używany do tworzenia wartości dla każdego wiersza w kolumnie Title , która określa jego poziom agregacji.
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żyj GROUPING_ID do filtrowania zbioru wyników
Przykład podstawowy
W następnym kodzie, aby zwrócić tylko wiersze, które mają liczbę pracowników według tytułu, usuń znaki komentarza z HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;. Aby zwracać tylko wiersze z liczbą pracowników według działów, usuń znaki komentarza z HAVING GROUPING_ID(D.Name, E.JobTitle) = 1;.
SELECT D.Name,
E.JobTitle,
GROUPING_ID(D.Name, E.JobTitle) AS [Grouping Level],
COUNT(E.BusinessEntityID) AS [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 zestaw wyników bez filtracji.
| Name | Title | Poziom grupowania | Liczba pracowników | Name |
|---|---|---|---|---|
| Kontrola dokumentów | Specjalista ds. sterowania | 0 | 2 | Kontrola dokumentów |
| Kontrola dokumentów | Asystent Kontroli Dokumentów | 0 | 2 | Kontrola dokumentów |
| Kontrola dokumentów | Menedżer Kontroli Dokumentów | 0 | 1 | Kontrola dokumentów |
| Kontrola dokumentów | NULL |
1 | 5 | Kontrola dokumentów |
| Obiekty i utrzymanie | Asystent administracyjny ds. obiektów | 0 | 1 | Obiekty i utrzymanie |
| Obiekty i utrzymanie | Kierownik obiektów | 0 | 1 | Obiekty i utrzymanie |
| Obiekty i utrzymanie | Dozorca | 0 | 4 | Obiekty i utrzymanie |
| Obiekty i utrzymanie | Kierownik utrzymania | 0 | 1 | Obiekty i utrzymanie |
| Obiekty i utrzymanie | NULL |
1 | 7 | Obiekty i utrzymanie |
NULL |
NULL |
3 | 12 | NULL |
Złożony przykład
Poniższy przykład służy GROUPING_ID() do filtrowania zbioru wyników zawierającego wiele poziomów grupowania według poziomu grupowania. Podobny kod można wykorzystać do stworzenia widoku składającego się z kilku poziomów grupowania oraz procedury przechowywanej, która wywołuje widok poprzez przekazanie parametru filtrującego widok według poziomu grupowania.
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. Użyj GROUPING_ID() z ROLLUP i CUBE, aby zidentyfikować poziomy grupowania
Kod w poniższych przykładach pokazuje używanie GROUPING() do obliczania kolumny Bit Vector(base-2) .
GROUPING_ID() jest używany do obliczenia odpowiadającej Integer Equivalent kolumny. Kolejność kolumn w funkcji GROUPING_ID() jest przeciwieństwem kolejności kolumn połączonych z funkcją GROUPING() .
W tych przykładach jest używany do tworzenia wartości dla każdego wiersza w kolumnieGROUPING_ID(), Grouping Level aby określić poziom grupowania. Poziomy grupowania nie zawsze są kolejną listą liczb całkowitych zaczynających się od 1 (0, 1, 2, ... n).
Uwaga / Notatka
GROUPING i może być użyty GROUPING_ID w klauzuli HAVING do filtrowania zbioru wyników.
Przykład ROLLUP
W tym przykładzie wszystkie poziomy grupowania nie pojawiają się tak, jak w poniższym CUBE przykładzie. Jeśli kolejność kolumn na ROLLUP liście zostanie zmieniona, należy również zmienić wartości poziomów w kolumnie Grouping Level .
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);
Oto zestaw wyników częściowych.
| Rok | Miesiąc | Day | Łączna należna | Wektor bitowy (baza-2) | Ekwiwalent liczb całkowitoliczbowych | 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 z CUBE
W tym przykładzie funkcja służy GROUPING_ID() do tworzenia wartości dla każdego wiersza w kolumnie Grouping Level , aby określić poziom grupowania.
W przeciwieństwie do ROLLUP poprzedniego przykładu, wychodzi CUBE na wszystkie poziomy grupowania. Jeśli kolejność kolumn na CUBE liście zostanie zmieniona, należy również zmienić wartości poziomów w kolumnie Grouping Level .
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);
Oto zestaw wyników częściowych.
| Rok | Miesiąc | Day | Łączna należna | Wektor bitowy (baza-2) | Ekwiwalent liczb całkowitoliczbowych | 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 | Day |
NULL |
NULL |
2 | 144513.3741 | 011 | 6 | Day |
NULL |
NULL |
NULL |
9364513.6416 | 111 | 7 | Suma końcowa |