在上一篇文章里我討論了SQL Server里Grouping Sets的功能。從文中的例子可以看到，通過簡單定義需要的分組集是很容易進行各自分組。但如果像從所給的列集里想要有所有可能的分布——即所謂的冪集（Power Set），要怎么做呢？

當然，你可以用grouping set的語法功能來手動生成冪集，但那需要寫一大堆的代碼。因此今天我向你展示下grouping set功能支持的2個從句：CUBE和ROLLUP從句。

使用CUBE從句，對于提供的列集，你可以生成所有可能的分組集。這就是所謂的冪集。當你有3列：a，b，和c。CUBE（a，b，c）會為你生成下列分組：

• （a，b，c）
• （a，b）
• （b，c）
• （a）
• （b）
• （c）
• （）

下列查詢對CustomerID, SalesPersonID和YEAR(OrderDate) 列通過上周介紹的grouping set功能手工生成冪集。

 1 -- Calculates the power set of CustomerID, SalesPersonID, YEAR(OrderDate) 2 SELECT 3     CustomerID,  4     SalesPersonID,  5     YEAR(OrderDate) AS 'OrderYear',  6     SUM(TotalDue) AS 'TotalDue' 7 FROM Sales.SalesOrderHeader 8 WHERE SalesPersonID IS NOT NULL 9 GROUP BY GROUPING SETS10 (11     (CustomerID, SalesPersonID, YEAR(OrderDate)),12     (CustomerID, SalesPersonID),13     (CustomerID, YEAR(OrderDate)),14     (SalesPersonID, YEAR(OrderDate)),15     (CustomerID),16     (SalesPersonID),17     (YEAR(OrderDate)),18     ()19 )20 GO

從代碼里可以看到，你必須指定每個可能的組合。因此用簡單的需求寫出這樣的查詢是個很困難的，笨重的工作。如果你使用CUBE從句而不是指定各個分組集的話，事情就變得簡單多了。我們來看下面的代碼。

 1 -- Calculates the power set of CustomerID, SalesPersonID, YEAR(OrderDate) with the CUBE subclause 2 SELECT 3     CustomerID,  4     SalesPersonID,  5     YEAR(OrderDate) AS 'OrderYear',  6     SUM(TotalDue) AS 'TotalDue' 7 FROM Sales.SalesOrderHeader 8 WHERE SalesPersonID IS NOT NULL 9 GROUP BY CUBE(CustomerID, SalesPersonID, YEAR(OrderDate))10 GO

從代碼里可以看到，你只要指定列，SQL Server本身就會生成它的冪集。于第一次列出的代碼，這個代碼簡單，精煉很多。

除CUBE從句外，自SQL Server 2008起，SQL Server也支持ROLLUP從句。使用ROLLUP從句你可以定義冪集的子集。ROLLUP從句也假設各個列間的層級。當你有3列：a，b，和c。當你使用ROLLUP（a，b，c），它會生成下列分組集：

• （a，b，c）
• （a，b）
• （a）
• （）

從這些獨立的分組集，你很容易看到在這些列之間有個層級。我們換用CustomerID, SalesPersonID和YEAR(OrderDate) 列，這里你就可以獲得這類分析查詢的實現思路。這是SSAS（SQL Server分析服務）的窮人做法。我們來看下列使用ROLLUP從句的查詢：

-- Calculates the following grouping sets:-- => (OrderYear, OrderMonth, OrderDay)-- => (OrderYear, OrderMonth)-- => (OrderYear)-- => ()SELECT    YEAR(OrderDate) AS 'OrderYear',     MONTH(OrderDate) AS 'OrderMonth',    DAY(OrderDate) AS 'OrderDay',    SUM(TotalDue) AS 'TotalDue'FROM Sales.SalesOrderHeaderWHERE SalesPersonID IS NOT NULLGROUP BY ROLLUP(YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate))GO

這個查詢的輸出給你下列各自分組集：

• (OrderYear, OrderMonth, OrderDay)
• (OrderYear, OrderMonth)
• (OrderYear)
• ()

ROLLUP從句有非常簡單的語法，但對于數據分析來說你的返回結果是非常強大的。

我希望你對今天文章里，自SQL Server 2008引入的grouping sets功能里的CUBE和ROLLUP子句的介紹有所收獲。有空的話，不要吝嗇你的留言，告訴我你是否已經在你自己的數據庫里使用這些從句，或者你是否認為在你的環境里它們是有用的。

感謝關注！