T-SQL 中的分组集 (SQL Server 2008)

引言

本文讨论了 T-SQL 中的分组集。分组集是 SQL Server 2008 中 T-SQL 的一项新功能。

背景

每当需要从任何结果集中获取 DISTINCT 时，人们都忍不住赞赏 GROUP BY 子句。此外，每当需要任何聚合函数时，GROUP BY 子句是唯一的解决方案。一直都有一个需求，即基于同一结果集中的不同列集获取这些聚合函数。使用此功能也是安全的，因为它是一个 ISO 标准。

虽然之前可以实现相同的结果，但我们需要编写不同的查询，并使用 UNION 运算符将它们组合起来。 GROUPING SET 返回的结果集是基于分组集中每个集合中指定的列的聚合的并集。

示例

为了完全理解它，我们首先创建一个表 tbl_Employee。

CREATE TABLE tbl_Employee 
( 
      Employee_Name varchar(25), 
      Region varchar(50), 
      Department varchar(40), 
      sal int 
)

现在，我们用以下一些行填充表

INSERT into tbl_Employee( 
                              Employee_Name, 
                              Region, 
                              Department, 
                              sal 
                        ) 

VALUES 

('Shujaat', 'North America', 'Information Technology', 9999), 
('Andrew', 'Asia Pacific', 'Information Technology',  5555), 
('Maria', 'North America', 'Human Resources', 4444), 
('Stephen', 'Middle East & Africa', 'Information Technology', 8888), 
('Stephen', 'Middle East & Africa', 'Human Resources', 8888)

填充行后，我们使用分组集选择一些行。

SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 
Group BY 
      GROUPING SETS 
      ( 
            (Region, Department), 
            (Region), 
            (Department) , 
            ()          
      )

此语句的结果如下

您可以看到结果集包含按指定分组集中每个集合分组的行。 您可以看到每个区域和部门的员工平均工资。 您还可以欣赏组织（对于区域和部门均为 NULL）的员工平均工资。 这是空分组集的结果，即 ()。

在 2008 年之前，如果您需要获得相同的结果集，则必须编写以下查询

SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 
Group BY 
      Region, Department 
UNION 


SELECT Region, NULL, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      Region 

UNION 

SELECT NULL, Department, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      Department 

UNION 

SELECT NULL, NULL, avg(sal) Average_Salary 

from tbl_Employee

通过查看上面的查询，您可以欣赏分组集为开发人员提供的便利。

用于分组的 CUBE 子句

这用于为 n 个元素返回 n 的 2 次方。

SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 
Group BY 
      CUBE (Region, Department)

上面的查询等效于以下查询

SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 
GROUPING SETS 
      ( 
            (Region, Department), 
            (Region), 
            (Department) , 
            ()          
      )

用于分组的 ROLLUP 子句

这用于为层次结构场景中的 n 个元素返回 n+1 个分组集。

SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      ROLLUP (Region, Department)

这等效于以下查询

SELECT Region, Department, avg(sal) Average_Salary 
from tbl_Employee 

Group BY 
      Grouping Sets 
      ( 
            (Region, Department), 
            (Region), 
            () 
      )