第一次看到這樣的SQL語句,看不懂,其中用到了下面的不常用的
聚集函式:GROUPING
用於彙總資料用的運算子: ROLLUP
SELECT
CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE "(Total)" END
AS AllCustomersSummary,
CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END
AS IndividualCustomerSummary,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = 1998 AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY AllCustomersSummary
檢視SQL Server的幫助才發現,厲害啊,原來還有這麼厲害的東西,不由的想起以前做水晶報表的時候,原來在SQL Server中就可以實現這樣的功能.
CUBE 運算子生成的結果集是多維資料集。多維資料集是事實資料的擴充套件，事實資料即記錄個別事件的資料。擴充套件建立在使用者打算分析的列上。這些列被稱為維。多維資料集是一個結果集，其中包含了各維度的所有可能組合的交叉表格。
CUBE 運算子在 SELECT 語句的 GROUP BY 子句中指定。該語句的選擇列表應包含維度列和聚合函式表示式。GROUP BY 應指定維度列和關鍵字 WITH CUBE。結果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的聚合值。
例如，一個簡單的表 Inventory 中包含：
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查詢返回的結果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計：
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
下面是結果集：
Item Color QtySum
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
我們著重考查下列各行：
這一行報告了 Item 維度中值為 Chair 的所有行的小計。對 Color 維度返回了 NULL 值，表示該行所報告的聚合包括 Color 維度為任意值的行。
這一行類似，但報告的是 Item 維度中值為 Table 的所有行的小計。
這一行報告了多維資料集的總計。Item 和 Color 維度的值都是 NULL，表示兩個維度中的所有值都彙總在該行中。
這兩行報告了 Color 維度的小計。兩行中的 Item 維度值都是 NULL，表示聚合資料來自 Item 維度為任意值的行。
使用 GROUPING 區分空值
CUBE 操作所生成的空值帶來一個問題：如何區分 CUBE 操作所生成的 NULL 值和從實際資料中返回的 NULL 值？這個問題可用 GROUPING 函式解決。如果列中的值來自事實資料，則 GROUPING 函式返回 0；如果列中的值是 CUBE 操作所生成的 NULL，則返回 1。在 CUBE 操作中，所生成的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函式將所生成的 NULL 替換為字串 ALL。因為事實資料中的 NULL 表明資料值未知，所以 SELECT 語句還可譯碼為返回字串 UNKNOWN 替代來自事實資料的 NULL。例如：
SELECT CASE WHEN (GROUPING(Item) = 1) THEN "ALL"
ELSE ISNULL(Item, "UNKNOWN")
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN "ALL"
ELSE ISNULL(Color, "UNKNOWN")
END AS Color,
SUM(Quantity) AS QtySum
多維資料集
CUBE 運算子可用於生成 n 維的多維資料集，即具有任意數目維度的多維資料集。只有一個維度的多維資料集可用於生成合計，例如：
GROUP BY Item WITH CUBE
GO
此 SELECT 語句返回的結果集既顯示了 Item 中每個值的小計，也顯示了 Item 中所有值的總計：
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
包含帶有許多維度的 CUBE 的 SELECT 語句可能生成很大的結果集，因為這些語句會為所有維度中值的所有組合生成行。這些大結果集包含的資料可能過多而不易於閱讀和理解。這個問題有一種解決辦法是將 SELECT 語句放在檢視中：
CREATE VIEW InvCube AS
然後即可用該檢視來只查詢您感興趣的維度值：
SELECT *
FROM InvCube
WHERE Item = "Chair"
AND Color = "ALL"
Chair ALL 311.00
(1 row(s) affected)
在生成包含小計和合計的報表時，ROLLUP 運算子很有用。ROLLUP 運算子生成的結果集類似於 CUBE 運算子所生成的結果集。有關更多資訊.
CUBE 和 ROLLUP 之間的區別在於：
例如，簡單表 Inventory 中包含：
下列查詢將生成小計報表：
GROUP BY Item, Color WITH ROLLUP
Table ALL 347.00
ALL ALL 658.00
(7 row(s) affected)
如果查詢中的 ROLLUP 關鍵字更改為 CUBE，那麼 CUBE 結果集與上述結果相同，只是在結果集的末尾還會返回下列兩行：
ALL Blue 225.00
ALL Red 433.00
CUBE 操作為 Item 和 Color 中值的可能組合生成行。例如，CUBE 不僅報告與 Item 值 Chair 相組合的 Color 值的所有可能組合（Red、Blue 和 Red + Blue），而且報告與 Color 值 Red 相組合的 Item 值的所有可能組合（Chair、Table 和 Chair + Table）。
對於 GROUP BY 子句中右邊的列中的每個值，ROLLUP 操作並不報告左邊一列（或左邊各列）中值的所有可能組合。例如，ROLLUP 並不對每個 Color 值報告 Item 值的所有可能組合。
ROLLUP 操作的結果集具有類似於 COMPUTE BY 所返回結果集的功能；然而，ROLLUP 具有下列優點：
是一個聚合函式,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算子新增行時，附加的列輸出值為1，當所新增的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。
僅在與包含 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 pubs
SELECT royalty, SUM(advance) "total advance",
GROUPING(royalty) "grp"FROM titles
GROUP BY royalty WITH ROLLUP
結果集在 royalty 下顯示兩個空值。第一個 NULL 代表從表中這一列得到的空值組。第二個 NULL 在 ROLLUP 操作所新增的彙總行中。彙總行顯示的是所有 royalty組的 advance 合計數值，並且在 grp 列中用 1 標識。
royalty total advance grp
--------- --------------------- ---
NULL NULL 0
10 57000.0000 0
12 2275.0000 0
14 4000.0000 0
16 7000.0000 0
24 25125.0000 0
NULL 95400.0000 1
第一次看到這樣的SQL語句,看不懂,其中用到了下面的不常用的
聚集函式:GROUPING
用於彙總資料用的運算子: ROLLUP
SELECT
CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE "(Total)" END
AS AllCustomersSummary,
CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END
AS IndividualCustomerSummary,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = 1998 AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY AllCustomersSummary
檢視SQL Server的幫助才發現,厲害啊,原來還有這麼厲害的東西,不由的想起以前做水晶報表的時候,原來在SQL Server中就可以實現這樣的功能.
1.用 CUBE 彙總資料CUBE 運算子生成的結果集是多維資料集。多維資料集是事實資料的擴充套件，事實資料即記錄個別事件的資料。擴充套件建立在使用者打算分析的列上。這些列被稱為維。多維資料集是一個結果集，其中包含了各維度的所有可能組合的交叉表格。
CUBE 運算子在 SELECT 語句的 GROUP BY 子句中指定。該語句的選擇列表應包含維度列和聚合函式表示式。GROUP BY 應指定維度列和關鍵字 WITH CUBE。結果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的聚合值。
例如，一個簡單的表 Inventory 中包含：
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查詢返回的結果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計：
SELECT Item, Color, SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
下面是結果集：
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair (null) 311.00
Table Blue 124.00
Table Red 223.00
Table (null) 347.00
(null) (null) 658.00
(null) Blue 225.00
(null) Red 433.00
我們著重考查下列各行：
Chair (null) 311.00
這一行報告了 Item 維度中值為 Chair 的所有行的小計。對 Color 維度返回了 NULL 值，表示該行所報告的聚合包括 Color 維度為任意值的行。
Table (null) 347.00
這一行類似，但報告的是 Item 維度中值為 Table 的所有行的小計。
(null) (null) 658.00
這一行報告了多維資料集的總計。Item 和 Color 維度的值都是 NULL，表示兩個維度中的所有值都彙總在該行中。
(null) Blue 225.00
(null) Red 433.00
這兩行報告了 Color 維度的小計。兩行中的 Item 維度值都是 NULL，表示聚合資料來自 Item 維度為任意值的行。
使用 GROUPING 區分空值
CUBE 操作所生成的空值帶來一個問題：如何區分 CUBE 操作所生成的 NULL 值和從實際資料中返回的 NULL 值？這個問題可用 GROUPING 函式解決。如果列中的值來自事實資料，則 GROUPING 函式返回 0；如果列中的值是 CUBE 操作所生成的 NULL，則返回 1。在 CUBE 操作中，所生成的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函式將所生成的 NULL 替換為字串 ALL。因為事實資料中的 NULL 表明資料值未知，所以 SELECT 語句還可譯碼為返回字串 UNKNOWN 替代來自事實資料的 NULL。例如：
SELECT CASE WHEN (GROUPING(Item) = 1) THEN "ALL"
ELSE ISNULL(Item, "UNKNOWN")
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN "ALL"
ELSE ISNULL(Color, "UNKNOWN")
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
多維資料集
CUBE 運算子可用於生成 n 維的多維資料集，即具有任意數目維度的多維資料集。只有一個維度的多維資料集可用於生成合計，例如：
SELECT CASE WHEN (GROUPING(Item) = 1) THEN "ALL"
ELSE ISNULL(Item, "UNKNOWN")
END AS Item,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item WITH CUBE
GO
此 SELECT 語句返回的結果集既顯示了 Item 中每個值的小計，也顯示了 Item 中所有值的總計：
Item QtySum
-------------------- --------------------------
Chair 311.00
Table 347.00
ALL 658.00
包含帶有許多維度的 CUBE 的 SELECT 語句可能生成很大的結果集，因為這些語句會為所有維度中值的所有組合生成行。這些大結果集包含的資料可能過多而不易於閱讀和理解。這個問題有一種解決辦法是將 SELECT 語句放在檢視中：
CREATE VIEW InvCube AS
SELECT CASE WHEN (GROUPING(Item) = 1) THEN "ALL"
ELSE ISNULL(Item, "UNKNOWN")
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN "ALL"
ELSE ISNULL(Color, "UNKNOWN")
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE
然後即可用該檢視來只查詢您感興趣的維度值：
SELECT *
FROM InvCube
WHERE Item = "Chair"
AND Color = "ALL"
Item Color QtySum
-------------------- -------------------- --------------------------
Chair ALL 311.00
(1 row(s) affected)
2.用 ROLLUP 彙總資料在生成包含小計和合計的報表時，ROLLUP 運算子很有用。ROLLUP 運算子生成的結果集類似於 CUBE 運算子所生成的結果集。有關更多資訊.
CUBE 和 ROLLUP 之間的區別在於：
CUBE 生成的結果集顯示了所選列中值的所有組合的聚合。ROLLUP 生成的結果集顯示了所選列中值的某一層次結構的聚合。例如，簡單表 Inventory 中包含：
Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210
下列查詢將生成小計報表：
SELECT CASE WHEN (GROUPING(Item) = 1) THEN "ALL"
ELSE ISNULL(Item, "UNKNOWN")
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN "ALL"
ELSE ISNULL(Color, "UNKNOWN")
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP
Item Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00
(7 row(s) affected)
如果查詢中的 ROLLUP 關鍵字更改為 CUBE，那麼 CUBE 結果集與上述結果相同，只是在結果集的末尾還會返回下列兩行：
ALL Blue 225.00
ALL Red 433.00
CUBE 操作為 Item 和 Color 中值的可能組合生成行。例如，CUBE 不僅報告與 Item 值 Chair 相組合的 Color 值的所有可能組合（Red、Blue 和 Red + Blue），而且報告與 Color 值 Red 相組合的 Item 值的所有可能組合（Chair、Table 和 Chair + Table）。
對於 GROUP BY 子句中右邊的列中的每個值，ROLLUP 操作並不報告左邊一列（或左邊各列）中值的所有可能組合。例如，ROLLUP 並不對每個 Color 值報告 Item 值的所有可能組合。
ROLLUP 操作的結果集具有類似於 COMPUTE BY 所返回結果集的功能；然而，ROLLUP 具有下列優點：
ROLLUP 返回單個結果集；COMPUTE BY 返回多個結果集，而多個結果集會增加應用程式程式碼的複雜性。ROLLUP 可以在伺服器遊標中使用；COMPUTE BY 不可以。有時，查詢最佳化器為 ROLLUP 生成的執行計劃比為 COMPUTE BY 生成的更為高效。3.GROUPING是一個聚合函式,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算子新增行時，附加的列輸出值為1，當所新增的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。
僅在與包含 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 pubs
SELECT royalty, SUM(advance) "total advance",
GROUPING(royalty) "grp"FROM titles
GROUP BY royalty WITH ROLLUP
結果集在 royalty 下顯示兩個空值。第一個 NULL 代表從表中這一列得到的空值組。第二個 NULL 在 ROLLUP 操作所新增的彙總行中。彙總行顯示的是所有 royalty組的 advance 合計數值，並且在 grp 列中用 1 標識。
下面是結果集：
royalty total advance grp
--------- --------------------- ---
NULL NULL 0
10 57000.0000 0
12 2275.0000 0
14 4000.0000 0
16 7000.0000 0
24 25125.0000 0
NULL 95400.0000 1
3.GROUPING是一個聚合函式,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算子新增行時，附加的列輸出值為1，當所新增的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。
僅在與包含 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 pubs
SELECT royalty, SUM(advance) "total advance",
GROUPING(royalty) "grp"FROM titles
GROUP BY royalty WITH ROLLUP
結果集在 royalty 下顯示兩個空值。第一個 NULL 代表從表中這一列得到的空值組。第二個 NULL 在 ROLLUP 操作所新增的彙總行中。彙總行顯示的是所有 royalty組的 advance 合計數值，並且在 grp 列中用 1 標識。
下面是結果集：
royalty total advance grp
--------- --------------------- ---
NULL NULL 0
10 57000.0000 0
12 2275.0000 0
14 4000.0000 0
16 7000.0000 0
24 25125.0000 0
NULL 95400.0000 1
3.GROUPING是一個聚合函式,它產生一個附加的列，當用 CUBE 或 ROLLUP 運算子新增行時，附加的列輸出值為1，當所新增的行不是由 CUBE 或 ROLLUP 產生時，附加列值為0。
僅在與包含 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 pubs
SELECT royalty, SUM(advance) "total advance",
GROUPING(royalty) "grp"FROM titles
GROUP BY royalty WITH ROLLUP
結果集在 royalty 下顯示兩個空值。第一個 NULL 代表從表中這一列得到的空值組。第二個 NULL 在 ROLLUP 操作所新增的彙總行中。彙總行顯示的是所有 royalty組的 advance 合計數值，並且在 grp 列中用 1 標識。
下面是結果集：
royalty total advance grp
--------- --------------------- ---
NULL NULL 0
10 57000.0000 0
12 2275.0000 0
14 4000.0000 0
16 7000.0000 0
24 25125.0000 0
NULL 95400.0000 1