GROUPING_ID (Transact-SQL)
这是计算分组级别的函数。仅当指定了 GROUP BY 时，GROUPING_ID 才能在 SELECT <select> 列表、HAVING 或 ORDER BY 子句中使用。
.gif "主题链接图标")
语法
GROUPING_ID ( <column_expression>[ ,...n ] )
返回类型
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。
例如，以下语句是等价的：
|
|
示例
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)。
.gif "注意") 注意 |
|---|
可以在 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 |