第一次看到這樣的SQL語句看不懂其中用到了下面的不常用的
聚集函數:GROUPING
用於匯總數據用的運算符: ROLLUP
SELECT
CASE GROUPING(ocustomerid) WHEN THEN ocustomerid ELSE (Total) END
AS AllCustomersSummary
CASE GROUPING(odorderid) WHEN THEN odorderid ELSE END
AS IndividualCustomerSummary
SUM(odquantity*odunitprice) AS price
FROM Orders o [Order Details] od
WHERE Year(oorderdate) = AND odorderid=oorderid
GROUP BY ocustomerid odorderid WITH ROLLUP
ORDER BY AllCustomersSummary
查看SQL Server的幫助才發現厲害啊原來還有這麼厲害的東西不由的想起以前做水晶報表的時候原來在SQL Server中就可以實現這樣的功能
用 CUBE 匯總數據
CUBE 運算符生成的結果集是多維數據集多維數據集是事實數據的擴展事實數據即記錄個別事件的數據擴展建立在用戶打算分析的列上這些列被稱為維多維數據集是一個結果集其中包含了各維度的所有可能組合的交叉表格
CUBE 運算符在 SELECT 語句的 GROUP BY 子句中指定該語句的選擇列表應包含維度列和聚合函數表達式GROUP BY 應指定維度列和關鍵字 WITH CUBE結果集將包含維度列中各值的所有可能組合以及與這些維度值組合相匹配的基礎行中的聚合值
例如一個簡單的表 Inventory 中包含
Item Color Quantity Table Blue Table Red Chair Blue Chair Red
下列查詢返回的結果集中將包含 Item 和 Color 的所有可能組合的 Quantity 小計
SELECT Item Color SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item Color WITH CUBE
下面是結果集
Item Color QtySum Chair Blue Chair Red Chair (null) Table Blue Table Red Table (null) (null) (null) (null) Blue (null) Red
我們著重考查下列各行
Chair (null)
這一行報告了 Item 維度中值為 Chair 的所有行的小計對 Color 維度返回了 NULL 值表示該行所報告的聚合包括 Color 維度為任意值的行
Table (null)
這一行類似但報告的是 Item 維度中值為 Table 的所有行的小計
(null) (null)
這一行報告了多維數據集的總計Item 和 Color 維度的值都是 NULL表示兩個維度中的所有值都匯總在該行中
(null) Blue (null) Red
這兩行報告了 Color 維度的小計兩行中的 Item 維度值都是 NULL表示聚合數據來自 Item 維度為任意值的行
使用 GROUPING 區分空值
CUBE 操作所生成的空值帶來一個問題如何區分 CUBE 操作所生成的 NULL 值和從實際數據中返回的 NULL 值？這個問題可用 GROUPING 函數解決如果列中的值來自事實數據則 GROUPING 函數返回 如果列中的值是 CUBE 操作所生成的 NULL則返回 在 CUBE 操作中所生成的 NULL 代表全體值可將 SELECT 語句寫成使用 GROUPING 函數將所生成的 NULL 替換為字符串 ALL因為事實數據中的 NULL 表明數據值未知所以 SELECT 語句還可譯碼為返回字符串 UNKNOWN 替代來自事實數據的 NULL例如
SELECT CASE WHEN (GROUPING(Item) = ) THEN ALL ELSE ISNULL(Item UNKNOWN) END AS Item CASE WHEN (GROUPING(Color) = ) THEN ALL ELSE ISNULL(Color UNKNOWN) END AS Color SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item Color WITH CUBE
多維數據集
CUBE 運算符可用於生成 n 維的多維數據集即具有任意數目維度的多維數據集只有一個維度的多維數據集可用於生成合計例如
SELECT CASE WHEN (GROUPING(Item) = ) THEN ALL ELSE ISNULL(Item UNKNOWN) END AS Item SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item WITH CUBEGO
此 SELECT 語句返回的結果集既顯示了 Item 中每個值的小計也顯示了 Item 中所有值的總計
Item QtySum Chair Table ALL
包含帶有許多維度的 CUBE 的 SELECT 語句可能生成很大的結果集因為這些語句會為所有維度中值的所有組合生成行這些大結果集包含的數據可能過多而不易於閱讀和理解這個問題有一種解決辦法是將 SELECT 語句放在視圖中
CREATE VIEW InvCube ASSELECT CASE WHEN (GROUPING(Item) = ) THEN ALL ELSE ISNULL(Item UNKNOWN) END AS Item CASE WHEN (GROUPING(Color) = ) THEN ALL ELSE ISNULL(Color UNKNOWN) END AS Color SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item Color WITH CUBE
然後即可用該視圖來只查詢您感興趣的維度值
SELECT *FROM InvCubeWHERE Item = Chair AND Color = ALLItem Color QtySum Chair ALL ( row(s) affected)
用 ROLLUP 匯總數據
在生成包含小計和合計的報表時ROLLUP 運算符很有用ROLLUP 運算符生成的結果集類似於 CUBE 運算符所生成的結果集有關更多信息
CUBE 和 ROLLUP 之間的區別在於
CUBE 生成的結果集顯示了所選列中值的所有組合的聚合
ROLLUP 生成的結果集顯示了所選列中值的某一層次結構的聚合
例如簡單表 Inventory 中包含
Item Color Quantity Table Blue Table Red Chair Blue Chair Red
下列查詢將生成小計報表
SELECT CASE WHEN (GROUPING(Item) = ) THEN ALL ELSE ISNULL(Item UNKNOWN) END AS Item CASE WHEN (GROUPING(Color) = ) THEN ALL ELSE ISNULL(Color UNKNOWN) END AS Color SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item Color WITH ROLLUPItem Color QtySum Chair Blue Chair Red Chair ALL Table Blue Table Red Table ALL ALL ALL ( row(s) affected)
如果查詢中的 ROLLUP 關鍵字更改為 CUBE那麼 CUBE 結果集與上述結果相同只是在結果集的末尾還會返回下列兩行
ALL Blue ALL Red
CUBE 操作為 Item 和 Color 中值的可能組合生成行例如CUBE 不僅報告與 Item 值 Chair 相組合的 Color 值的所有可能組合（RedBlue 和 Red + Blue）而且報告與 Color 值 Red 相組合的 Item 值的所有可能組合（ChairTable 和 Chair + Table）
對於 GROUP BY 子句中右邊的列中的每個值ROLLUP 操作並不報告左邊一列（或左邊各列）中值的所有可能組合例如ROLLUP 並不對每個 Color 值報告 Item 值的所有可能組合
ROLLUP 操作的結果集具有類似於 COMPUTE BY 所返回結果集的功能然而ROLLUP 具有下列優點
ROLLUP 返回單個結果集
COMPUTE BY 返回多個結果集
而多個結果集會增加應用程序代碼的復雜性
ROLLUP 可以在服務器游標中使用
COMPUTE BY 不可以
有時
查詢優化器為 ROLLUP 生成的執行計劃比為 COMPUTE BY 生成的更為高效
GROUPING
是一個聚合函數它產生一個附加的列當用 CUBE 或 ROLLUP 運算符添加行時附加的列輸出值為當所添加的行不是由 CUBE 或 ROLLUP 產生時附加列值為
僅在與包含 CUBE 或 ROLLUP 運算符的 GROUP BY 子句相聯系的選擇列表中才允許分組
語法
GROUPING ( column_name )
參數
column_name
是 GROUP BY 子句中用於檢查 CUBE 或 ROLLUP 空值的列
返回類型
int
注釋
分組用於區分由 CUBE 和 ROLLUP 返回的空值和標准的空值作為CUBE 或 ROLLUP 操作結果返回的 NULL 是 NULL 的特殊應用它在結果集內作為列的占位符意思是全體
示例
下面的示例將 royalty 的數值分組並聚合 advance 的數值GROUPING 函數應用於 royalty 列
USE pubsSELECT royalty SUM(advance) total advance GROUPING(royalty) grp FROM titles GROUP BY royalty WITH ROLLUP
結果集在 royalty 下顯示兩個空值第一個 NULL 代表從表中這一列得到的空值組第二個 NULL 在 ROLLUP 操作所添加的匯總行中匯總行顯示的是所有 royalty 組的 advance 合計數值並且在 grp 列中用 標識
下面是結果集
royalty total advance grp NULL NULL NULL
GROUPING
是一個聚合函數它產生一個附加的列當用 CUBE 或 ROLLUP 運算符添加行時附加的列輸出值為當所添加的行不是由 CUBE 或 ROLLUP 產生時附加列值為
僅在與包含 CUBE 或 ROLLUP 運算符的 GROUP BY 子句相聯系的選擇列表中才允許分組
語法
GROUPING ( column_name )
參數
column_name
是 GROUP BY 子句中用於檢查 CUBE 或 ROLLUP 空值的列
返回類型
int
注釋
分組用於區分由 CUBE 和 ROLLUP 返回的空值和標准的空值作為CUBE 或 ROLLUP 操作結果返回的 NULL 是 NULL 的特殊應用它在結果集內作為列的占位符意思是全體
示例
下面的示例將 royalty 的數值分組並聚合 advance 的數值GROUPING 函數應用於 royalty 列
USE pubsSELECT royalty SUM(advance) total advance GROUPING(royalty) grp FROM titles GROUP BY royalty WITH ROLLUP
結果集在 royalty 下顯示兩個空值第一個 NULL 代表從表中這一列得到的空值組第二個 NULL 在 ROLLUP 操作所添加的匯總行中匯總行顯示的是所有 royalty 組的 advance 合計數值並且在 grp 列中用 標識
下面是結果集
royalty total advance grp NULL NULL NULL
GROUPING
是一個聚合函數它產生一個附加的列當用 CUBE 或 ROLLUP 運算符添加行時附加的列輸出值為當所添加的行不是由 CUBE 或 ROLLUP 產生時附加列值為
僅在與包含 CUBE 或 ROLLUP 運算符的 GROUP BY 子句相聯系的選擇列表中才允許分組
語法
GROUPING ( column_name )
參數
column_name
是 GROUP BY 子句中用於檢查 CUBE 或 ROLLUP 空值的列
返回類型
int
注釋
分組用於區分由 CUBE 和 ROLLUP 返回的空值和標准的空值作為CUBE 或 ROLLUP 操作結果返回的 NULL 是 NULL 的特殊應用它在結果集內作為列的占位符意思是全體
示例
下面的示例將 royalty 的數值分組並聚合 advance 的數值GROUPING 函數應用於 royalty 列
USE pubsSELECT royalty SUM(advance) total advance GROUPING(royalty) grp FROM titles GROUP BY royalty WITH ROLLUP
結果集在 royalty 下顯示兩個空值第一個 NULL 代表從表中這一列得到的空值組第二個 NULL 在 ROLLUP 操作所添加的匯總行中匯總行顯示的是所有 royalty 組的 advance 合計數值並且在 grp 列中用 標識
下面是結果集
royalty total advance grp NULL NULL NULL
對GROUPINGROLLUPCUBE的介紹來自SQL Server中文版的幫助
From:http://tw.wingwit.com/Article/program/MySQL/201404/30565.html
