SQLServer中匯總功效的應用GROUPING,ROLLUP和CUBE。本站提示廣大學習愛好者：（SQLServer中匯總功效的應用GROUPING,ROLLUP和CUBE）文章只能為提供參考，不一定能成為您想要的結果。以下是SQLServer中匯總功效的應用GROUPING,ROLLUP和CUBE正文

---

第一次看到如許的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 QtySumFROM InventoryGROUP 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 QtySumFROM InventoryGROUP 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 QtySumFROM InventoryGROUP BY Item WITH CUBEGO

此 SELECT 語句前往的成果集既顯示了 Item 中每一個值的小計，也顯示了 Item 中一切值的總計：

 

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

包括帶有很多維度的 CUBE 的 SELECT 語句能夠生成很年夜的成果集，由於這些語句會為一切維度中值的一切組合生成行。這些年夜成果集包括的數據能夠過量而不容易於浏覽和懂得。這個成績有一種處理方法是將 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 匯總數據

在生成包括小計和算計的報表時，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 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 症結字更改成 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 pubsSELECT 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 pubsSELECT 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 pubsSELECT 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 

對GROUPING,ROLLUP,CUBE的引見來自SQL Server2000中文版的贊助.