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.
GROUPING_ID ( <column_expression>[ ,...n ] )
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:
|
|
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);
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 |
1 |
4947613 |
010 |
2 |
Dzień roku |
2003 |
WARTOŚCI NULL |
2 |
43456.76 |
010 |
2 |
Dzień roku |
2004 |
WARTOŚCI NULL |
1 |
5892402 |
010 |
2 |
Dzień roku |
2004 |
WARTOŚCI NULL |
2 |
101056.6 |
010 |
2 |
Dzień 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 |
1 |
1 |
4001589 |
001 |
4 |
Dzień miesiąca |
WARTOŚCI NULL |
1 |
2 |
68230.42 |
001 |
4 |
Dzień miesiąca |
WARTOŚCI NULL |
2 |
1 |
6838427 |
001 |
4 |
Dzień miesiąca |
WARTOŚCI NULL |
2 |
2 |
76282.96 |
001 |
4 |
Dzień miesiąca |
WARTOŚCI NULL |
1 |
WARTOŚCI NULL |
4069819 |
101 |
5 |
Miesiąc |
WARTOŚCI NULL |
2 |
WARTOŚCI NULL |
6914710 |
101 |
5 |
Miesiąc |
WARTOŚCI NULL |
WARTOŚCI NULL |
1 |
10840016 |
011 |
6 |
Dzień |
WARTOŚCI NULL |
WARTOŚCI NULL |
2 |
144513.4 |
011 |
6 |
Dzień |
WARTOŚCI NULL |
WARTOŚCI NULL |
WARTOŚCI NULL |
10984529 |
111 |
7 |
Suma końcowa |