MySQL :: MySQL 9.0 參考手冊 :: 14.19.2 GROUP BY 修飾詞

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / GROUP BY 修飾詞

14.19.2 GROUP BY 修飾詞

GROUP BY 子句允許使用 WITH ROLLUP 修飾詞，使摘要輸出包含代表更高等級（即超彙總）摘要操作的額外資料列。ROLLUP 因此可讓您使用單一查詢回答多個分析層級的問題。例如，ROLLUP 可用於提供 OLAP（線上分析處理）操作的支援。

假設 sales 表格具有 year、country、product 和 profit 欄，用於記錄銷售利潤。

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

若要摘要每個年份的表格內容，請使用像這樣的簡單 GROUP BY

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

輸出會顯示每個年份的總（彙總）利潤。若要也判斷所有年份的總利潤總和，您必須自己加總個別值，或執行額外查詢。或者，您可以使用 ROLLUP，它可使用單一查詢提供兩個分析層級。將 WITH ROLLUP 修飾詞新增至 GROUP BY 子句，會使查詢產生另一個（超彙總）資料列，顯示所有年份值的總計

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

year 欄中的 NULL 值會識別總計超彙總行。

當有多個 GROUP BY 欄時，ROLLUP 的效果會更複雜。在此情況下，每次在最後一個分組欄以外的任何欄中的值發生變更時，查詢都會產生額外的超彙總摘要列。

例如，在沒有 ROLLUP 的情況下，根據 year、country 和 product 的 sales 表格摘要可能如下所示，其中輸出只表示年份/國家/產品分析層級的摘要值

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+

新增 ROLLUP 後，查詢會產生數個額外資料列

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

現在輸出包含四個分析層級的摘要資訊，而不只是一個層級

在給定年份和國家/地區的每一組產品列之後，會出現額外的超彙總摘要列，顯示所有產品的總計。這些資料列的 product 欄設定為 NULL。
在給定年份的每一組資料列之後，會出現額外的超彙總摘要列，顯示所有國家/地區和產品的總計。這些資料列的 country 和 products 欄設定為 NULL。
最後，在所有其他資料列之後，會出現額外的超彙總摘要列，顯示所有年份、國家/地區和產品的總計。此資料列的 year、country 和 products 欄設定為 NULL。

當資料列傳送至用戶端時，會產生每個超彙總資料列中的 NULL 指示器。伺服器會查看 GROUP BY 子句中，在值已變更的最左邊欄之後命名的欄。對於結果集中名稱符合任何這些名稱的任何欄，其值會設定為 NULL。（如果您依欄位置指定分組欄，伺服器會依位置識別要設定為 NULL 的欄。）

由於超彙總資料列中的 NULL 值是在查詢處理的後階段放置到結果集中，因此您只能在選取清單或 HAVING 子句中將它們測試為 NULL 值。您無法在聯結條件或 WHERE 子句中將它們測試為 NULL 值，以判斷要選取哪些資料列。例如，您無法將 WHERE product IS NULL 新增至查詢，以從輸出中消除除超彙總資料列以外的所有資料列。

NULL 值在用戶端會顯示為 NULL，而且可以使用任何 MySQL 用戶端程式設計介面將其測試為這樣。不過，此時，您無法區分 NULL 代表的是一般分組值還是超彙總值。若要測試區別，請使用稍後說明的 GROUPING() 函數。

對於 GROUP BY ... WITH ROLLUP 查詢，若要測試結果中的 NULL 值是否代表超聚合值，可在 select list、HAVING 子句和 ORDER BY 子句中使用 GROUPING() 函數。例如，當 year 資料行中的 NULL 出現在超聚合列時，GROUPING(year) 會傳回 1，否則傳回 0。類似地，當 country 和 product 資料行中出現超聚合 NULL 值時，GROUPING(country) 和 GROUPING(product) 會分別傳回 1。

mysql> SELECT
         year, country, product, SUM(profit) AS profit,
         GROUPING(year) AS grp_year,
         GROUPING(country) AS grp_country,
         GROUPING(product) AS grp_product
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+----------+-------------+-------------+
| year | country | product    | profit | grp_year | grp_country | grp_product |
+------+---------+------------+--------+----------+-------------+-------------+
| 2000 | Finland | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | Finland | Phone      |    100 |        0 |           0 |           0 |
| 2000 | Finland | NULL       |   1600 |        0 |           0 |           1 |
| 2000 | India   | Calculator |    150 |        0 |           0 |           0 |
| 2000 | India   | Computer   |   1200 |        0 |           0 |           0 |
| 2000 | India   | NULL       |   1350 |        0 |           0 |           1 |
| 2000 | USA     | Calculator |     75 |        0 |           0 |           0 |
| 2000 | USA     | Computer   |   1500 |        0 |           0 |           0 |
| 2000 | USA     | NULL       |   1575 |        0 |           0 |           1 |
| 2000 | NULL    | NULL       |   4525 |        0 |           1 |           1 |
| 2001 | Finland | Phone      |     10 |        0 |           0 |           0 |
| 2001 | Finland | NULL       |     10 |        0 |           0 |           1 |
| 2001 | USA     | Calculator |     50 |        0 |           0 |           0 |
| 2001 | USA     | Computer   |   2700 |        0 |           0 |           0 |
| 2001 | USA     | TV         |    250 |        0 |           0 |           0 |
| 2001 | USA     | NULL       |   3000 |        0 |           0 |           1 |
| 2001 | NULL    | NULL       |   3010 |        0 |           1 |           1 |
| NULL | NULL    | NULL       |   7535 |        1 |           1 |           1 |
+------+---------+------------+--------+----------+-------------+-------------+

您可以不直接顯示 GROUPING() 的結果，而是使用 GROUPING() 來替換超聚合 NULL 值為標籤。

mysql> SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS product,
         SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+-----------+---------------+--------------+--------+
| year      | country       | product      | profit |
+-----------+---------------+--------------+--------+
| 2000      | Finland       | Computer     |   1500 |
| 2000      | Finland       | Phone        |    100 |
| 2000      | Finland       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

當有多個表達式引數時，GROUPING() 會傳回一個結果，此結果代表一個位元遮罩，其中結合了每個表達式的結果，最低位元對應到最右邊表達式的結果。例如，GROUPING(year, country, product) 的評估方式如下。

  result for GROUPING(product)
+ result for GROUPING(country) << 1
+ result for GROUPING(year) << 2

如果任何表達式代表超聚合 NULL，則此 GROUPING() 的結果將為非零值，因此您可以只傳回超聚合列，並像這樣篩除常規的分組列。

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL    |   1600 |
| 2000 | India   | NULL    |   1350 |
| 2000 | USA     | NULL    |   1575 |
| 2000 | NULL    | NULL    |   4525 |
| 2001 | Finland | NULL    |     10 |
| 2001 | USA     | NULL    |   3000 |
| 2001 | NULL    | NULL    |   3010 |
| NULL | NULL    | NULL    |   7535 |
+------+---------+---------+--------+

sales 表格不包含 NULL 值，因此 ROLLUP 結果中的所有 NULL 值都代表超聚合值。當資料集中包含 NULL 值時，ROLLUP 摘要可能會在超聚合列和常規分組列中都包含 NULL 值。GROUPING() 可以區分這些值。假設表格 t1 包含一組簡單的資料集，其中包含一組數量值的兩個分組因素，其中 NULL 表示類似「其他」或「未知」之類的值。

mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | small |       10 |
| ball | large |       20 |
| ball | NULL  |        5 |
| hoop | small |       15 |
| hoop | large |        5 |
| hoop | NULL  |        3 |
+------+-------+----------+

一個簡單的 ROLLUP 操作會產生這些結果，其中很難區分超聚合列中的 NULL 值和常規分組列中的 NULL 值。

mysql> SELECT name, size, SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | NULL  |        5 |
| ball | large |       20 |
| ball | small |       10 |
| ball | NULL  |       35 |
| hoop | NULL  |        3 |
| hoop | large |        5 |
| hoop | small |       15 |
| hoop | NULL  |       23 |
| NULL | NULL  |       58 |
+------+-------+----------+

使用 GROUPING() 來替換超聚合 NULL 值為標籤，可以使結果更容易理解。

mysql> SELECT
         IF(GROUPING(name) = 1, 'All items', name) AS name,
         IF(GROUPING(size) = 1, 'All sizes', size) AS size,
         SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name      | size      | quantity |
+-----------+-----------+----------+
| ball      | NULL      |        5 |
| ball      | large     |       20 |
| ball      | small     |       10 |
| ball      | All sizes |       35 |
| hoop      | NULL      |        3 |
| hoop      | large     |        5 |
| hoop      | small     |       15 |
| hoop      | All sizes |       23 |
| All items | All sizes |       58 |
+-----------+-----------+----------+

使用 ROLLUP 時的其他考量

以下討論列出 MySQL 實作 ROLLUP 的一些特定行為。

ORDER BY 和 ROLLUP 可以一起使用，這使得能夠使用 ORDER BY 和 GROUPING() 來實現分組結果的特定排序。例如：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP
       ORDER BY GROUPING(year) DESC;
+------+--------+
| year | profit |
+------+--------+
| NULL |   7535 |
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

在這兩種情況下，超聚合摘要列都與計算它們的列一起排序，它們的位置取決於排序順序（升序排序時位於末尾，降序排序時位於開頭）。

LIMIT 可用於限制返回給用戶端的列數。LIMIT 在 ROLLUP 之後應用，因此限制適用於 ROLLUP 添加的額外列。例如：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       LIMIT 5;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
+------+---------+------------+--------+

將 LIMIT 與 ROLLUP 一起使用可能會產生更難以理解的結果，因為理解超聚合列的上下文較少。

MySQL 擴充功能允許在 select list 中命名未出現在 GROUP BY 清單中的資料行。(有關非聚合資料行和 GROUP BY 的資訊，請參閱第 14.19.3 節「MySQL 的 GROUP BY 處理」。) 在這種情況下，伺服器可以自由地在摘要列中選擇此非聚合資料行的任何值，其中包括 WITH ROLLUP 添加的額外列。例如，在以下查詢中，country 是一個未出現在 GROUP BY 清單中的非聚合資料行，並且為此資料行選擇的值是不確定的。

mysql> SELECT year, country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

當未啟用 ONLY_FULL_GROUP_BY SQL 模式時，允許此行為。如果啟用該模式，伺服器會因 country 未列在 GROUP BY 子句中而拒絕該查詢為非法。啟用 ONLY_FULL_GROUP_BY 時，您仍然可以透過對不確定值的資料行使用 ANY_VALUE() 函數來執行查詢。

mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

彙總資料行不能作為 MATCH() 的引數（且會以錯誤拒絕），除非在 WHERE 子句中呼叫時除外。有關更多資訊，請參閱第 14.9 節「全文搜尋函數」。