查看SQL Server的幫助才發(fā)現(xiàn),厲害啊,原來還有這么厲害的東西,不由的想起以前做水晶報(bào)表的時(shí)候,原來在SQL Server中就可以實(shí)現(xiàn)這樣的功能.

第一次看到這樣的SQL語句,看不懂,其中用到了下面的不常用的

聚集函數(shù):GROUPING

用于匯總數(shù)據(jù)用的運(yùn)算符: 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

1.用 CUBE 匯總數(shù)據(jù)

CUBE 運(yùn)算符生成的結(jié)果集是多維數(shù)據(jù)集。多維數(shù)據(jù)集是事實(shí)數(shù)據(jù)的擴(kuò)展，事實(shí)數(shù)據(jù)即記錄個(gè)別事件的數(shù)據(jù)。擴(kuò)展建立在用戶打算分析的列上。這些列被稱為維。多維數(shù)據(jù)集是一個(gè)結(jié)果集，其中包含了各維度的所有可能組合的交叉表格。

CUBE 運(yùn)算符在 SELECT 語句的 GROUP BY 子句中指定。該語句的選擇列表應(yīng)包含維度列和聚合函數(shù)表達(dá)式。GROUP BY 應(yīng)指定維度列和關(guān)鍵字 WITH CUBE。結(jié)果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎(chǔ)行中的聚合值。

例如，一個(gè)簡(jiǎn)單的表 Inventory 中包含：

Item         Color        Quantity          -------------------- -------------------- -------------------------- Table        Blue         124            Table        Red         223            Chair        Blue         101            Chair        Red         210

下列查詢返回的結(jié)果集中，將包含 Item 和 Color 的所有可能組合的 Quantity 小計(jì)：

SELECT Item, Color, SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item, Color WITH CUBE

下面是結(jié)果集：

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

這一行報(bào)告了 Item 維度中值為 Chair 的所有行的小計(jì)。對(duì) Color 維度返回了 NULL 值，表示該行所報(bào)告的聚合包括 Color 維度為任意值的行。

Table        (null)        347.00

這一行類似，但報(bào)告的是 Item 維度中值為 Table 的所有行的小計(jì)。

(null)        (null)        658.00

這一行報(bào)告了多維數(shù)據(jù)集的總計(jì)。Item 和 Color 維度的值都是 NULL，表示兩個(gè)維度中的所有值都匯總在該行中。

(null)        Blue         225.00           (null)        Red         433.00

這兩行報(bào)告了 Color 維度的小計(jì)。兩行中的 Item 維度值都是 NULL，表示聚合數(shù)據(jù)來自 Item 維度為任意值的行。

使用 GROUPING 區(qū)分空值

CUBE 操作所生成的空值帶來一個(gè)問題：如何區(qū)分 CUBE 操作所生成的 NULL 值和從實(shí)際數(shù)據(jù)中返回的 NULL 值？這個(gè)問題可用 GROUPING 函數(shù)解決。如果列中的值來自事實(shí)數(shù)據(jù)，則 GROUPING 函數(shù)返回 0；如果列中的值是 CUBE 操作所生成的 NULL，則返回 1。在 CUBE 操作中，所生成的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函數(shù)將所生成的 NULL 替換為字符串 ALL。因?yàn)槭聦?shí)數(shù)據(jù)中的 NULL 表明數(shù)據(jù)值未知，所以 SELECT 語句還可譯碼為返回字符串 UNKNOWN 替代來自事實(shí)數(shù)據(jù)的 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 QtySumFROM InventoryGROUP BY Item, Color WITH CUBE

多維數(shù)據(jù)集

CUBE 運(yùn)算符可用于生成 n 維的多維數(shù)據(jù)集，即具有任意數(shù)目維度的多維數(shù)據(jù)集。只有一個(gè)維度的多維數(shù)據(jù)集可用于生成合計(jì)，例如：

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'      ELSE ISNULL(Item, 'UNKNOWN')    END AS Item,    SUM(Quantity) AS QtySumFROM InventoryGROUP BY Item WITH CUBEGO

此 SELECT 語句返回的結(jié)果集既顯示了 Item 中每個(gè)值的小計(jì)，也顯示了 Item 中所有值的總計(jì)：

Item         QtySum           -------------------- -------------------------- Chair        311.00           Table        347.00           ALL         658.00

包含帶有許多維度的 CUBE 的 SELECT 語句可能生成很大的結(jié)果集，因?yàn)檫@些語句會(huì)為所有維度中值的所有組合生成行。這些大結(jié)果集包含的數(shù)據(jù)可能過多而不易于閱讀和理解。這個(gè)問題有一種解決辦法是將 SELECT 語句放在視圖中：

CREATE VIEW InvCube ASSELECT 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 QtySumFROM InventoryGROUP BY Item, Color WITH CUBE

然后即可用該視圖來只查詢您感興趣的維度值：

SELECT *FROM InvCubeWHERE Item = 'Chair' AND Color = 'ALL'Item         Color        QtySum           -------------------- -------------------- -------------------------- Chair        ALL         311.00           (1 row(s) affected)

2.用 ROLLUP 匯總數(shù)據(jù)

在生成包含小計(jì)和合計(jì)的報(bào)表時(shí)，ROLLUP 運(yùn)算符很有用。ROLLUP 運(yùn)算符生成的結(jié)果集類似于 CUBE 運(yùn)算符所生成的結(jié)果集。有關(guān)更多信息.

CUBE 和 ROLLUP 之間的區(qū)別在于：

CUBE 生成的結(jié)果集顯示了所選列中值的所有組合的聚合。
ROLLUP 生成的結(jié)果集顯示了所選列中值的某一層次結(jié)構(gòu)的聚合。

例如，簡(jiǎn)單表 Inventory 中包含：

Item         Color        Quantity          -------------------- -------------------- -------------------------- Table        Blue         124            Table        Red         223            Chair        Blue         101            Chair        Red         210

下列查詢將生成小計(jì)報(bào)表：

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 QtySumFROM InventoryGROUP BY Item, Color WITH ROLLUPItem         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 關(guān)鍵字更改為 CUBE，那么 CUBE 結(jié)果集與上述結(jié)果相同，只是在結(jié)果集的末尾還會(huì)返回下列兩行：

ALL         Blue         225.00           ALL         Red         433.00

CUBE 操作為 Item 和 Color 中值的可能組合生成行。例如，CUBE 不僅報(bào)告與 Item 值 Chair 相組合的 Color 值的所有可能組合（Red、Blue 和 Red + Blue），而且報(bào)告與 Color 值 Red 相組合的 Item 值的所有可能組合（Chair、Table 和 Chair + Table）。

對(duì)于 GROUP BY 子句中右邊的列中的每個(gè)值，ROLLUP 操作并不報(bào)告左邊一列（或左邊各列）中值的所有可能組合。例如，ROLLUP 并不對(duì)每個(gè) Color 值報(bào)告 Item 值的所有可能組合。

ROLLUP 操作的結(jié)果集具有類似于 COMPUTE BY 所返回結(jié)果集的功能；然而，ROLLUP 具有下列優(yōu)點(diǎn)：

ROLLUP 返回單個(gè)結(jié)果集；COMPUTE BY 返回多個(gè)結(jié)果集，而多個(gè)結(jié)果集會(huì)增加應(yīng)用程序代碼的復(fù)雜性。
ROLLUP 可以在服務(wù)器游標(biāo)中使用；COMPUTE BY 不可以。
有時(shí)，查詢優(yōu)化器為 ROLLUP 生成的執(zhí)行計(jì)劃比為 COMPUTE BY 生成的更為高效。

3.GROUPING

是一個(gè)聚合函數(shù),它產(chǎn)生一個(gè)附加的列，當(dāng)用 CUBE 或 ROLLUP 運(yùn)算符添加行時(shí)，附加的列輸出值為1，當(dāng)所添加的行不是由 CUBE 或 ROLLUP 產(chǎn)生時(shí)，附加列值為0。

僅在與包含 CUBE 或 ROLLUP 運(yùn)算符的 GROUP BY 子句相聯(lián)系的選擇列表中才允許分組。

語法

GROUPING ( column_name )

參數(shù)

column_name

是 GROUP BY 子句中用于檢查 CUBE 或 ROLLUP 空值的列。

返回類型

int

注釋

分組用于區(qū)分由 CUBE 和 ROLLUP 返回的空值和標(biāo)準(zhǔn)的空值。作為CUBE 或 ROLLUP 操作結(jié)果返回的 NULL 是 NULL 的特殊應(yīng)用。它在結(jié)果集內(nèi)作為列的占位符，意思是”全體”。

示例

下面的示例將 royalty 的數(shù)值分組，并聚合 advance 的數(shù)值。GROUPING 函數(shù)應(yīng)用于 royalty 列。

<CODE>USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'</CODE>  <CODE>FROM titles  GROUP BY royalty WITH ROLLUP</CODE>

結(jié)果集在 royalty 下顯示兩個(gè)空值。第一個(gè) NULL 代表從表中這一列得到的空值組。第二個(gè) NULL 在 ROLLUP 操作所添加的匯總行中。匯總行顯示的是所有 royalty 組的 advance 合計(jì)數(shù)值，并且在 grp 列中用 1 標(biāo)識(shí)。

下面是結(jié)果集：

<CODE>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 </CODE>

3.GROUPING

僅在與包含 CUBE 或 ROLLUP 運(yùn)算符的 GROUP BY 子句相聯(lián)系的選擇列表中才允許分組。

語法

GROUPING ( column_name )

參數(shù)

column_name

是 GROUP BY 子句中用于檢查 CUBE 或 ROLLUP 空值的列。

返回類型

int

注釋

示例

下面的示例將 royalty 的數(shù)值分組，并聚合 advance 的數(shù)值。GROUPING 函數(shù)應(yīng)用于 royalty 列。

<CODE>USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'</CODE>  <CODE>FROM titles  GROUP BY royalty WITH ROLLUP</CODE>

下面是結(jié)果集：

<CODE>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 </CODE>

3.GROUPING

僅在與包含 CUBE 或 ROLLUP 運(yùn)算符的 GROUP BY 子句相聯(lián)系的選擇列表中才允許分組。

語法

GROUPING ( column_name )

參數(shù)

column_name

是 GROUP BY 子句中用于檢查 CUBE 或 ROLLUP 空值的列。

返回類型

int

注釋

示例

下面的示例將 royalty 的數(shù)值分組，并聚合 advance 的數(shù)值。GROUPING 函數(shù)應(yīng)用于 royalty 列。

<CODE>USE pubsSELECT royalty, SUM(advance) 'total advance',  GROUPING(royalty) 'grp'</CODE>  <CODE>FROM titles  GROUP BY royalty WITH ROLLUP</CODE>

下面是結(jié)果集：

<CODE>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 </CODE>

對(duì)GROUPING,ROLLUP,CUBE的介紹來自SQL Server2000中文版的幫助.

? 版權(quán)聲明

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載。

THE END