GROUPING_ID (Transact-SQL)

文章
10/16/2024

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

GROUPING_ID 這是計算群組層級的函式。 GROUPING_ID只有在指定時GROUP BY，才能在清單、HAVING或 ORDER BY 子句中使用SELECT <select>。

語法

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

引數

<column_expression>

SELECT - GROUP BY 子句中的column_expression。

傳回類型

int

備註

必須 GROUPING_ID <column_expression> 完全符合清單中的表達式 GROUP BY 。例如，如果您要依 DATEPART (yyyy, <column name>)群組，請使用 GROUPING_ID (DATEPART (yyyy, <column name>));或者，如果您要依 <column name>分組，請使用 GROUPING_ID (<column name>)。

比較GROUPING_ID（）與 GROUPING（）

GROUPING_ID (<column_expression> [ , ...n ]) 輸入每個輸出數據列中每個數據行清單中每個數據行的傳回對等 GROUPING (<column_expression>) 專案，做為一和零的字串。 GROUPING_ID 會將該字串解譯為base-2數位，並傳回相等的整數。

例如，請考慮下列語句：

SELECT a, b, c, SUM(d),
GROUPING_ID(a, b, c)
FROM T
GROUP BY <group_by_list>

下表顯示 GROUPING_ID() 輸入和輸出值。

彙總資料行	GROUPING_ID (a, b, c) 輸入 = GROUPING(a) + GROUPING(b) + GROUPING(c)	GROUPING_ID（）輸出
`a`	`100`	`4`
`b`	`010`	`2`
`c`	`001`	`1`
`ab`	`110`	`6`
`ac`	`101`	`5`
`bc`	`011`	`3`
`abc`	`111`	`7`

GROUPING_ID的技術定義（）

每個 GROUPING_ID 自變數都必須是清單的 GROUP BY 元素。 GROUPING_ID() 會傳回整數位陣圖，其最低 n 位元可能為點數。點亮位表示對應的自變數不是指定輸出數據列的群組數據行。最低順序位會對應至自變數 n，而第 n-1 個最低順序位會對應至自變數 1。

GROUPING_ID（）對等專案

對於單一群組查詢， GROUPING (<column_expression>) 相當於 GROUPING_ID (<column_expression>)，且兩者都會傳回 0。

例如，下列陳述式是相等的：

語句 A

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

語句 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

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 使用GROUPING_ID來識別群組層級

下列範例會依 Name 和 Title傳回資料庫中的員工總數和公司總計 AdventureWorks2022 。 GROUPING_ID() 會用來針對 Title 資料行中識別彙總層級的每一個資料列各建立一個值。

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. 使用GROUPING_ID來篩選結果集

基本範例

在下列程式代碼中，若要只傳回具有標題員工計數的數據列，請從 HAVING GROUPING_ID(D.Name, E.JobTitle) = 0;中移除批註字元。若只要依據部門傳回具有員工計數的資料列，請從 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;

以下是未篩選的結果集。

名稱	Title	Grouping Level	Employee Count	名稱
Document Control	Control Specialist	0	2	Document Control
Document Control	Document Control Assistant	0	2	Document Control
Document Control	Document Control Manager	0	1	Document Control
Document Control	`NULL`	1	5	Document Control
Facilities and Maintenance	Facilities Administrative Assistant	0	1	Facilities and Maintenance
Facilities and Maintenance	Facilities Manager	0	1	Facilities and Maintenance
Facilities and Maintenance	Janitor	0	4	Facilities and Maintenance
Facilities and Maintenance	Maintenance Supervisor	0	1	Facilities and Maintenance
Facilities and Maintenance	`NULL`	1	7	Facilities and Maintenance
`NULL`	`NULL`	3	12	`NULL`

複雜範例

下列範例使用 GROUPING_ID() 篩選包含多個群組層級的結果集 (藉由群組層級)。類似的程式代碼可用來建立具有數個群組層級的檢視，以及藉由傳遞參數來篩選檢視的群組層級來呼叫檢視的預存程式。

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. 搭配 ROLLUP 和 CUBE 使用 GROUPING_ID（）來識別群組層級

下列範例中的程式代碼示範如何使用 GROUPING() 來計算數據 Bit Vector(base-2) 行。 GROUPING_ID() 是用來計算對應的 Integer Equivalent 資料行。 GROUPING_ID() 函數中的資料行順序與 GROUPING() 函數串連之資料行的資料行順序相反。

在這些範例中，GROUPING_ID() 是用來針對 Grouping Level 資料行中的每一個資料列各建立一個值，以識別群組的層級。群組層級不一定是以 1 開頭的連續整數清單（0、1、2...n）。

注意

GROUPING 和 GROUPING_ID 可用於子句來 HAVING 篩選結果集。

ROLLUP 範例

在此範例中，所有群組層級不會如下列 CUBE 範例所示出現。如果清單中數據 ROLLUP 行的順序有所變更，數據行中的 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);

以下為部分結果集。

Year	Month	Day	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452.6066	000	0	Year Month Day
2007	1	2	21772.3494	000	0	Year Month Day
2007	2	1	2705653.5913	000	0	Year Month Day
2007	2	2	21684.4068	000	0	Year Month Day
2008	1	1	1908122.0967	000	0	Year Month Day
2008	1	2	46458.0691	000	0	Year Month Day
2008	2	1	3108771.9729	000	0	Year Month Day
2008	2	2	54598.5488	000	0	Year Month Day
2007	1	`NULL`	1519224.956	100	1	Year Month
2007	2	`NULL`	2727337.9981	100	1	Year Month
2008	1	`NULL`	1954580.1658	100	1	Year Month
2008	2	`NULL`	3163370.5217	100	1	Year Month
2007	`NULL`	`NULL`	4246562.9541	110	3	Year
2008	`NULL`	`NULL`	5117950.6875	110	3	Year
`NULL`	`NULL`	`NULL`	9364513.6416	111	7	總計

CUBE 範例

在此範例中，GROUPING_ID() 函數是用來針對 Grouping Level 資料行中的每一個資料列各建立一個值，以識別群組的層級。

與之前範例中的 ROLLUP 不同，CUBE 會輸出所有群組層級。如果清單中數據 CUBE 行的順序有所變更，數據行中的 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);

以下為部分結果集。

Year	Month	Day	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2007	1	1	1497452.6066	000	0	Year Month Day
2007	1	2	21772.3494	000	0	Year Month Day
2007	2	1	2705653.5913	000	0	Year Month Day
2007	2	2	21684.4068	000	0	Year Month Day
2008	1	1	1908122.0967	000	0	Year Month Day
2008	1	2	46458.0691	000	0	Year Month Day
2008	2	1	3108771.9729	000	0	Year Month Day
2008	2	2	54598.5488	000	0	Year Month Day
2007	1	`NULL`	1519224.956	100	1	Year Month
2007	2	`NULL`	2727337.9981	100	1	Year Month
2008	1	`NULL`	1954580.1658	100	1	Year Month
2008	2	`NULL`	3163370.5217	100	1	Year Month
2007	`NULL`	1	4203106.1979	010	2	Year Day
2007	`NULL`	2	43456.7562	010	2	Year Day
2008	`NULL`	1	5016894.0696	010	2	Year Day
2008	`NULL`	2	101056.6179	010	2	Year Day
2007	`NULL`	`NULL`	4246562.9541	110	3	Year
2008	`NULL`	`NULL`	5117950.6875	110	3	Year
`NULL`	1	1	3405574.7033	001	4	Month Day
`NULL`	1	2	68230.4185	001	4	Month Day
`NULL`	2	1	5814425.5642	001	4	Month Day
`NULL`	2	2	76282.9556	001	4	Month Day
`NULL`	1	`NULL`	3473805.1218	101	5	Month
`NULL`	2	`NULL`	5890708.5198	101	5	Month
`NULL`	`NULL`	1	9220000.2675	011	6	Day
`NULL`	`NULL`	2	144513.3741	011	6	Day
`NULL`	`NULL`	`NULL`	9364513.6416	111	7	總計

分享方式：

GROUPING_ID (Transact-SQL)

語法

引數

<column_expression>

傳回類型

備註

比較GROUPING_ID（）與 GROUPING（）

GROUPING_ID的技術定義（）

GROUPING_ID（）對等專案

語句 A

語句 B

範例

A. 使用GROUPING_ID來識別群組層級

B. 使用GROUPING_ID來篩選結果集

基本範例

複雜範例

C. 搭配 ROLLUP 和 CUBE 使用 GROUPING_ID（）來識別群組層級

ROLLUP 範例

CUBE 範例

意見反映

更多資源

分享方式：

GROUPING_ID (Transact-SQL)

語法

引數

<column_expression>

傳回類型

備註

比較GROUPING_ID（） 與 GROUPING（）

GROUPING_ID的技術定義（）

GROUPING_ID（） 對等專案

語句 A

語句 B

範例

A. 使用GROUPING_ID來識別群組層級

B. 使用GROUPING_ID來篩選結果集

基本範例

複雜範例

C. 搭配 ROLLUP 和 CUBE 使用 GROUPING_ID（） 來識別群組層級

ROLLUP 範例

CUBE 範例

相關內容

意見反映

更多資源

比較GROUPING_ID（）與 GROUPING（）

GROUPING_ID（）對等專案

C. 搭配 ROLLUP 和 CUBE 使用 GROUPING_ID（）來識別群組層級