T-SQL GROUPING SETS （新特性）

/*********************************************************
Author="WZ"
Create Date="2008/9/7"
SQL SERVER 2008 开发系列（十二）T-SQL GROUPING SETS （新特性）
**********************************************************/

/*********************************************************
第一章:目标
通过本节内容,了解T-SQL中的GROUP BY 的应用,主要包括以下:
1: CUBE
2: ROLLUP
3: GROUPING SETS
**********************************************************/

--本节以AdventureWorks数据库为示例
USE AdventureWorks
GO

--普通的聚合查询
SELECT
    DATEPART(yyyy,OrderDate) AS N'Year',
    DATEPART(mm, OrderDate) AS 'Month',
    SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate), DATEPART(mm, OrderDate)
ORDER BY DATEPART(yyyy,OrderDate), DATEPART(mm, OrderDate)

--ROLLUP 的聚合查询
SELECT
    DATEPART(yyyy,OrderDate) AS N'Year',
    DATEPART(mm, OrderDate) AS 'Month',
    SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY ROLLUP((DATEPART(yyyy,OrderDate)), (DATEPART(mm, OrderDate))) --标准语法
ORDER BY DATEPART(yyyy,OrderDate), DATEPART(mm, OrderDate)

--如何通过GROUPING SETS 替换ROLLUP查询
SELECT
    DATEPART(yyyy,OrderDate) AS N'Year',
    DATEPART(mm, OrderDate) AS 'Month',
    SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS(
                        DATEPART(yyyy,OrderDate),
                        (DATEPART(yyyy,OrderDate),
                        DATEPART(mm, OrderDate))
                      ) --标准GROUPING SETS语法
ORDER BY DATEPART(yyyy,OrderDate), DATEPART(mm, OrderDate)

--CUBE 的查询
SELECT
    DATEPART(yyyy,OrderDate) AS N'Year',
    DATEPART(mm, OrderDate) AS 'Month',
    SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY CUBE((DATEPART(yyyy,OrderDate)), (DATEPART(mm, OrderDate)))
ORDER BY DATEPART(yyyy,OrderDate), DATEPART(mm, OrderDate)

--使用 GROUPING SET 替换 CUBE 查询
SELECT
    DATEPART(yyyy,OrderDate) AS N'Year',
    DATEPART(mm, OrderDate) AS 'Month',
    SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY GROUPING SETS(    (),
                        DATEPART(mm, OrderDate),
                        DATEPART(yyyy,OrderDate),
                        (DATEPART(yyyy,OrderDate), DATEPART(mm, OrderDate))
                      )
ORDER BY DATEPART(yyyy,OrderDate), DATEPART(mm, OrderDate)