GROUPING_ID (Transact-SQL)

这是计算分组级别的函数。仅当指定了 GROUP BY 时，GROUPING_ID 才能在 SELECT <select> 列表、HAVING 或 ORDER BY 子句中使用。

Transact-SQL 语法约定

语法

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

参数

• <column_expression>
  是 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>) 在每个输出行中为其列列表中的每个列返回的对应值作为 0、1 字符串输入。GROUPING_ID 将该字符串解释为二进制数并返回对应的整数。例如，请参考以下语句：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 () 返回一个 integer 位图，其最低 N 位可能为文字。文字 bit 表明对应参数不是给定输出行的分组列。最低顺序 bit 对应于参数 N，第 N-1 个最低顺序 bit 对应于参数 1。

GROUPING_ID () 等效项

对于单个分组查询，GROUPING (<column_expression>) 与 GROUPING_ID (<column_expression>) 等价，两者均返回 0。

例如，以下语句是等价的：

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

示例

A. 使用 GROUPING_ID 标识分组级别

下面的示例返回按 Name 和 Title 汇总的雇员计数以及 Name 和公司总计。GROUPING_ID() 用于为 Title 列中的每行创建一个值以标识聚合级别。

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. 使用 GROUPING_ID 筛选结果集

简单示例

在以下代码中，若要只返回包含按职务汇总的雇员计数的行，请删除 HAVING GROUPING_ID(D.Name, E.Title); = 0 中的注释字符。若要只返回包含按部门汇总的雇员计数的行，请删除 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

下面是未筛选的结果集。

名称	标题	分组级别	雇员计数	名称
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() 用于按分组级别筛选包含多个分组级别的结果集。可以使用类似的代码创建一个具有多个分组级别的视图和调用该视图（通过传递一个按分组级别筛选该视图的参数）的存储过程。

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. 将 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)。

注意
可以在 HAVING 子句中使用 GROUPING 和 GROUPING_ID 来筛选结果集。

ROLLUP 示例

在此示例中，不会像在下面的 CUBE 示例中那样显示所有分组级别。如果更改 ROLLUP 列表中列的顺序，则也必须更改 Grouping Level 列中的级别值。

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);

以下为部分结果集。

Year	Month	Day	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2003	1	1	1762381	000	0	Year Month Day
2003	1	2	21772.35	000	0	Year Month Day
2003	2	1	3185233	000	0	Year Month Day
2003	2	2	21684.41	000	0	Year Month Day
2004	1	1	2239208	000	0	Year Month Day
2004	1	2	46458.07	000	0	Year Month Day
2004	2	1	3653194	000	0	Year Month Day
2004	2	2	54598.55	000	0	Year Month Day
2003	1	NULL	1784153	100	1	Year Month
2003	2	NULL	3206917	100	1	Year Month
2004	1	NULL	2285666	100	1	Year Month
2004	2	NULL	3707793	100	1	Year Month
2003	NULL	NULL	4991070	110	3	Year
2004	NULL	NULL	5993459	110	3	Year
NULL	NULL	NULL	10984529	111	7	Grand Total

CUBE 示例

在此示例中，GROUPING_ID() 函数用于为 Grouping Level 列中的每行创建一个值以标识分组级别。

与上例中的 ROLLUP 不同，CUBE 会输出所有分组级别。如果更改 CUBE 列表中列的顺序，则也必须更改 Grouping Level 列中的级别值。

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);

以下为部分结果集。

Year	Month	Day	Total Due	Bit Vector (base-2)	Integer Equivalent	Grouping Level
2003	1	1	1762381	000	0	Year Month Day
2003	1	2	21772.35	000	0	Year Month Day
2003	2	1	3185233	000	0	Year Month Day
2003	2	2	21684.41	000	0	Year Month Day
2004	1	1	2239208	000	0	Year Month Day
2004	1	2	46458.07	000	0	Year Month Day
2004	2	1	3653194	000	0	Year Month Day
2004	2	2	54598.55	000	0	Year Month Day
2003	1	NULL	1784153	100	1	Year Month
2003	2	NULL	3206917	100	1	Year Month
2004	1	NULL	2285666	100	1	Year Month
2004	2	NULL	3707793	100	1	Year Month
2003	NULL	1	4947613	010	2	Year Day
2003	NULL	2	43456.76	010	2	Year Day
2004	NULL	1	5892402	010	2	Year Day
2004	NULL	2	101056.6	010	2	Year Day
2003	NULL	NULL	4991070	110	3	Year
2004	NULL	NULL	5993459	110	3	Year
NULL	1	1	4001589	001	4	Month Day
NULL	1	2	68230.42	001	4	Month Day
NULL	2	1	6838427	001	4	Month Day
NULL	2	2	76282.96	001	4	Month Day
NULL	1	NULL	4069819	101	5	Month
NULL	2	NULL	6914710	101	5	Month
NULL	NULL	1	10840016	011	6	Day
NULL	NULL	2	144513.4	011	6	Day
NULL	NULL	NULL	10984529	111	7	Grand Total

请参阅

参考

GROUPING (Transact-SQL)

GROUP BY (Transact-SQL)

概念

将 GROUP BY 与 ROLLUP、CUBE 和 GROUPING SETS 一起使用

GROUPING SETS 等效项

GROUP BY 错误故障排除