SQL ROLLUP

摘要: 在本教學中，您將學習如何使用 SQL ROLLUP 來產生多個分組集合。

SQL ROLLUP 簡介

ROLLUP 是 GROUP BY 子句的擴展。 ROLLUP 選項允許您包含代表小計的額外行，這些行通常稱為超級彙總行，以及總計行。透過使用 ROLLUP 選項，您可以使用單一查詢來產生多個分組集合。

請注意，分組集合是您要分組的欄位的集合。 例如，一個查詢返回依倉庫劃分的庫存，則分組集合是（倉庫）。

SELECT
    warehouse, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse;Code language: SQL (Structured Query Language) (sql)

有關 GROUPING SETS 的更多信息，請查看分組集合教學。

以下說明 SQL ROLLUP 的基本語法

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);
Code language: SQL (Structured Query Language) (sql)

ROLLUP 假設輸入欄位之間存在層次結構。 例如，如果輸入欄位是（c1,c2），則層次結構為 c1 > c2。ROLLUP 會產生考慮到此層次結構的所有合理分組集合。 這就是我們經常使用 ROLLUP 來產生報表所需的小計和總計的原因。

在上面的語法中，ROLLUP(c1,c2) 會產生以下三個分組集合

(c1,c2)
(c1)
()Code language: SQL (Structured Query Language) (sql)

Oracle、Microsoft SQL Server 和 PostgreSQL 都支援此語法。 但是，MySQL 的語法略有不同，如下所示

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table_name
GROUP BY c1, c2 WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)

SQL ROLLUP 範例

我們將使用在 GROUPING SETS 教學中建立的 inventory 表格進行示範。

SQL ROLLUP 與單一欄位範例

以下陳述式使用 GROUP BY 子句和 SUM() 函數來查找依倉庫劃分的總庫存

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY warehouse;
Code language: SQL (Structured Query Language) (sql)

要檢索所有倉庫中的總產品數，請將 ROLLUP 新增至 GROUP BY 子句，如下所示

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

正如您在結果中看到的那樣，warehouse 欄中的 NULL 值指定了總計的超級彙總行。 在此範例中，ROLLUP 選項會使查詢產生另一行，顯示所有倉庫中的總產品數。

為了使輸出更具可讀性，您可以使用 COALESCE() 函數將 NULL 值替換為 所有倉庫，如下所示

SELECT 
    COALESCE(warehouse, 'All warehouses') AS warehouse,
    SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

SQL ROLLUP 與多個欄位範例

以下陳述式計算依倉庫和產品劃分的庫存

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, product;
Code language: SQL (Structured Query Language) (sql)

讓我們將 ROLLUP 新增到 GROUP BY 子句中

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse , product);
Code language: SQL (Structured Query Language) (sql)

請注意，輸出包含兩個分析層次的摘要資訊，而不僅僅是一個

SQL ROLLUP 與部分彙總範例

您可以使用 ROLLUP 來執行部分彙總，從而減少計算的小計數量，如下例所示

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, ROLLUP (product);
Code language: SQL (Structured Query Language) (sql)

在此範例中，ROLLUP 僅為 product 欄建立超級彙總摘要，而不為 warehouse 欄建立。

在本教學中，您已學習如何使用 SQL ROLLUP 透過單一查詢執行多個層次的分析。