MS SQL Server 2008 中的分组集

引言

SQL Server 2008 的一项出色的新特性是分组集。分组集是对 GROUP BY 子句的扩展，它允许用户在同一个查询中定义多个分组。

关于分组集的事实

在开始使用分组集之前，您需要了解以下几点：

1. 分组集：一组用于分组的列。
2. 有助于支持聚合的动态分析。
3. 早于 2008 年的 SQL Server 版本对分组集的支持有限。
4. 生成单个结果集，等效于对不同分组行的 UNION ALL。
5. 使聚合查询和报告更轻松、更快速。

示例

-- Use UNION ALL on dual SELECT statements
SELECT CustomerType, Null AS TerritoryID, MAX(ModifiedDate)
FROM dbo.tbl_Customer GROUP BY CustomerType
UNION ALL
SELECT Null AS CustomerType, TerritoryID, MAX(ModifiedDate)
FROM dbo.tbl_Customer GROUP BY TerritoryID
ORDER BY TerritoryID

-- Use GROUPING SETS on single SELECT statement
SELECT CustomerType, TerritoryID, MAX(ModifiedDate)
FROM dbo.tbl_Customer
GROUP BY GROUPING SETS ((CustomerType), (TerritoryID)) 
ORDER BY CustomerType

SELECT 
       d.Year, d.Quarter, t.Country, SUM(f.SalesAmount) AS SalesAmount
FROM 
       dbo.tblSales AS f INNER JOIN 
       dbo.tblSDate AS d ON f.SDateID = d.SDateID INNER JOIN 
       dbo.tblSTerritory AS t ON f.STerritoryID = t.STerritoryID
WHERE 
       d.Year IN (2006, 2007)
       GROUP BY GROUPING SETS ( 
                (Year, Quarter, Country), 
                (Year, Quarter) ,
                (Country), 
                () )
       ORDER BY 
                Year, Quarter, Country