SQL ROLLUP

摘要：在本教程中，您將學習如何使用 SQL ROLLUP 來返回多個詳細級別的聚合結果。

SQL ROLLUP 簡介 #

GROUP BY 子句允許您根據一列或多列中的值將行分組。

然而，在聚合函式中，GROUP BY 子句只能返回單一詳細級別的聚合結果。

要返回多個詳細級別的聚合結果，您需要將 ROLLUP 與 GROUP BY 子句一起使用。

SELECT
  column1,
  column2,
  aggregate_function (column3)
FROM
  table_name
GROUP BY
  ROLLUP (column1, column2);Code language: SQL (Structured Query Language) (sql)

在此語法中，您將要分組的列放在 ROLLUP 關鍵字後的括號內。

ROLLUP 假設 column1 和 column2 之間存在層次結構。

column1 > column2Code language: SQL (Structured Query Language) (sql)

因此，ROLLUP 有助於生成包含小計和總計的報表。

ROLLUP 的工作原理

• 首先，從 GROUP BY 子句中指定的最詳細的分組開始。
• 其次，透過移除列並計算小計來向上移動層次結構。
• 第三，以所有行的總計行結束。

SQL ROLLUP 示例 #

我們將使用 salary_reports 表來說明 ROLLUP 的工作原理。

對單列使用 ROLLUP #

以下示例使用 ROLLUP 計算每個州以及所有州的總薪水。

SELECT
  state,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state)
ORDER BY
  state NULLS LAST;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

   state    | total_salary
------------+--------------
 California |    280000.00
 Texas      |    180000.00
 NULL       |    460000.00Code language: SQL (Structured Query Language) (sql)

輸出包括每個州（加利福尼亞州和德克薩斯州）的總薪水以及所有州的總薪水。

NULL 表示所有州的總計行。如果您想使用更有意義的標籤，可以使用 COALESCE 函式。

SELECT
  COALESCE(state, 'Total') state,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state)
ORDER BY
  state NULLS LAST;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

   state    | total_salary
------------+--------------
 California |    280000.00
 Texas      |    180000.00
 Total      |    460000.00Code language: SQL (Structured Query Language) (sql)

對多列使用 ROLLUP #

以下語句使用 ROLLUP 計算每個州和每個職位的小計薪水以及總計薪水。

SELECT
  state,
  job,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state, job)
ORDER BY
  state NULLS LAST;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

   state    |    job    | total_salary
------------+-----------+--------------
 California | IT        |    150000.00
 California | Marketing |    130000.00
 California | NULL      |    280000.00
 Texas      | IT        |    100000.00
 Texas      | Marketing |     80000.00
 Texas      | NULL      |    180000.00
 NULL       | NULL      |    460000.00Code language: SQL (Structured Query Language) (sql)

與前面的示例一樣，您可以將 NULL 更改為更有意義的標籤。

SELECT
  COALESCE(state, '') state,
  COALESCE(job, '') job,
  SUM(salary) total_salary
FROM
  salary_reports
GROUP BY
  ROLLUP (state, job)
ORDER BY
  state DESC,
  job DESC;Code language: SQL (Structured Query Language) (sql)

試一試

輸出

   state    |    job    | total_salary
------------+-----------+--------------
 Texas      | Marketing |     80000.00
 Texas      | IT        |    100000.00
 Texas      |           |    180000.00
 California | Marketing |    130000.00
 California | IT        |    150000.00
 California |           |    280000.00
            |           |    460000.00Code language: SQL (Structured Query Language) (sql)

摘要 #

• 使用 SQL ROLLUP 返回多個詳細級別的聚合結果。

測驗 #

資料庫 #

• PostgreSQL ROLLUP
• Oracle ROLLUP
• SQL Server GROUPING SETS