• 项目

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 AdventureWorks2008R2;
GO
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; 中的注释字符。

USE AdventureWorks2008R2;
GO
SELECT D.Name
    ,E.JobTitle
    ,GROUPING_ID(D.Name, E.JobTitle) AS 'Grouping Level'
    ,COUNT(E.BusinessEntityID) AS N'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;

下面是未筛选的结果集。

名称

职务

分组级别

雇员计数

名称

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

注意注意

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

ROLLUP 示例

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

USE AdventureWorks2008R2;
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'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

Grand Total

CUBE 示例

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

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

USE AdventureWorks2008R2;
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'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

Grand Total