GROUPING_ID (Transact-SQL)

项目
10/16/2024

适用于： SQL Server Azure SQL 数据库 Azure SQL 托管实例

GROUPING_ID 计算分组级别的函数。 GROUPING_ID只能在指定时GROUP BY在列表或ORDER BYHAVING子句中使用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 示例和社区项目主页下载它。

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;

下面是未筛选的结果集。

名称	标题	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 将 GROUPING_ID（）与 ROLLUP 和 CUBE 配合使用来标识分组级别

以下示例中的代码演示如何使用 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);

以下为部分结果集。

年龄	月份	日期	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	年龄
2008	`NULL`	`NULL`	5117950.6875	110	3	年龄
`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);

以下为部分结果集。

年龄	月份	日期	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	年龄
2008	`NULL`	`NULL`	5117950.6875	110	3	年龄
`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	月
`NULL`	2	`NULL`	5890708.5198	101	5	月
`NULL`	`NULL`	1	9220000.2675	011	6	日期
`NULL`	`NULL`	2	144513.3741	011	6	日期
`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 将 GROUPING_ID（）与 ROLLUP 和 CUBE 配合使用来标识分组级别

ROLLUP 示例

CUBE 示例

反馈

其他资源

通过

GROUPING_ID (Transact-SQL)

语法

参数

<column_expression>

返回类型

注解

比较GROUPING_ID（） 与 GROUPING（）

GROUPING_ID的技术定义（）

GROUPING_ID（） 等效项

语句 A

语句 B

示例

A. 使用GROUPING_ID标识分组级别

B. 使用GROUPING_ID筛选结果集

基本示例

复杂示例

°C 将 GROUPING_ID（） 与 ROLLUP 和 CUBE 配合使用来标识分组级别

ROLLUP 示例

CUBE 示例

相关内容

反馈

其他资源

比较GROUPING_ID（）与 GROUPING（）

GROUPING_ID（）等效项

°C 将 GROUPING_ID（）与 ROLLUP 和 CUBE 配合使用来标识分组级别