MySQL :: MySQL 9.0 參考手冊 :: 14.19.3 MySQL 的 GROUP BY 處理

版本 9.0

8.4 目前
8.0
5.7

8.0 日語

MySQL 9.0 參考手冊 / ... / MySQL 的 GROUP BY 處理

14.19.3 MySQL 的 GROUP BY 處理

SQL-92 和更早版本不允許查詢的選取清單、HAVING 條件或 ORDER BY 清單參照未在 GROUP BY 子句中命名的非聚合欄位。例如，此查詢在標準 SQL-92 中是非法的，因為選取清單中未聚合的 name 欄位未出現在 GROUP BY 中

SELECT o.custid, c.name, MAX(o.payment)
  FROM orders AS o, customers AS c
  WHERE o.custid = c.custid
  GROUP BY o.custid;

為了使查詢在 SQL-92 中合法，必須從選取清單中省略 name 欄位，或在 GROUP BY 子句中命名它。

如果非聚合欄位在功能上相依於 GROUP BY 欄位，則 SQL:1999 及更高版本允許每個選用功能 T301 使用此類非聚合欄位：如果 name 和 custid 之間存在這種關係，則查詢是合法的。例如，如果 custid 是 customers 的主索引鍵，情況就會如此。

MySQL 實作功能相依性的偵測。如果啟用 ONLY_FULL_GROUP_BY SQL 模式 (預設為啟用)，MySQL 會拒絕選取清單、HAVING 條件或 ORDER BY 清單參照未在 GROUP BY 子句中命名，且在功能上不相依於它們的非聚合欄位的查詢。

當啟用 SQL ONLY_FULL_GROUP_BY 模式時，如果該欄位僅限於單一值，MySQL 也允許在 GROUP BY 子句中未命名的非聚合欄位，如下列範例所示

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 1000),
    ->        (2, 'abc', 2000),
    ->        (3, 'def', 4000);

mysql> SET SESSION sql_mode = sys.list_add(@@session.sql_mode, 'ONLY_FULL_GROUP_BY');

mysql> SELECT a, SUM(b) FROM mytable WHERE a = 'abc';
+------+--------+
| a    | SUM(b) |
+------+--------+
| abc  |   3000 |
+------+--------+

使用 ONLY_FULL_GROUP_BY 時，也可以在 SELECT 清單中擁有多個非聚合欄位。在這種情況下，每個此類欄位都必須在 WHERE 子句中限制為單一值，且所有此類限制條件都必須由邏輯 AND 連接，如下所示

mysql> DROP TABLE IF EXISTS mytable;

mysql> CREATE TABLE mytable (
    ->    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ->    a VARCHAR(10),
    ->    b VARCHAR(10),
    ->    c INT
    -> );

mysql> INSERT INTO mytable
    -> VALUES (1, 'abc', 'qrs', 1000),
    ->        (2, 'abc', 'tuv', 2000),
    ->        (3, 'def', 'qrs', 4000),
    ->        (4, 'def', 'tuv', 8000),
    ->        (5, 'abc', 'qrs', 16000),
    ->        (6, 'def', 'tuv', 32000);

mysql> SELECT @@session.sql_mode;
+---------------------------------------------------------------+
| @@session.sql_mode                                            |
+---------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------+

mysql> SELECT a, b, SUM(c) FROM mytable
    ->     WHERE a = 'abc' AND b = 'qrs';
+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| abc  | qrs  |  17000 |
+------+------+--------+

如果停用 ONLY_FULL_GROUP_BY，MySQL 對標準 SQL 的 GROUP BY 用法的擴充功能允許選取清單、HAVING 條件或 ORDER BY 清單參照非聚合欄位，即使這些欄位在功能上不相依於 GROUP BY 欄位也是如此。這會導致 MySQL 接受前面的查詢。在這種情況下，伺服器可以自由地從每個群組中選擇任何值，因此，除非它們相同，否則所選的值是不確定的，這可能不是您想要的。此外，從每個群組選擇的值不會受到新增 ORDER BY 子句的影響。結果集排序會在選擇值後發生，而 ORDER BY 不會影響伺服器選擇每個群組內的哪個值。當您知道由於資料的某些屬性，未在 GROUP BY 中命名的每個非聚合欄位中的所有值對於每個群組都相同時，停用 ONLY_FULL_GROUP_BY 主要很有用。

您可以使用 ANY_VALUE() 來參照非聚合欄位，而無需停用 ONLY_FULL_GROUP_BY 來達到相同的效果。

下列討論示範功能相依性、MySQL 在缺少功能相依性時產生的錯誤訊息，以及導致 MySQL 在缺少功能相依性的情況下接受查詢的方法。

由於選取清單中未聚合的 address 欄位未在 GROUP BY 子句中命名，因此啟用 ONLY_FULL_GROUP_BY 時，此查詢可能無效

SELECT name, address, MAX(age) FROM t GROUP BY name;

如果 name 是 t 的主索引鍵，或是唯一的 NOT NULL 欄位，則查詢有效。在這種情況下，MySQL 會識別選取的欄位在功能上相依於群組欄位。例如，如果 name 是主索引鍵，則其值會決定 address 的值，因為每個群組只有一個主索引鍵的值，因此只有一個資料列。因此，群組中 address 值的選擇沒有隨機性，因此不需要拒絕查詢。

如果 name 不是 t 的主索引鍵或唯一的 NOT NULL 欄位，則查詢無效。在這種情況下，無法推斷出功能相依性，並且會發生錯誤

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause; this
is incompatible with sql_mode=only_full_group_by

如果您知道 對於給定的資料集，每個 name 值實際上都唯一決定 address 值，則 address 在功能上有效地相依於 name。為了告知 MySQL 接受查詢，您可以使用 ANY_VALUE() 函式

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

或者，停用 ONLY_FULL_GROUP_BY。

然而，前面的範例非常簡單。特別是，您不太可能在單一主索引鍵欄位上群組，因為每個群組都只包含一個資料列。如需在更複雜的查詢中示範功能相依性的其他範例，請參閱第 14.19.4 節「功能相依性的偵測」。

如果查詢具有聚合函式，且沒有 GROUP BY 子句，則在啟用 ONLY_FULL_GROUP_BY 的情況下，選取清單、HAVING 條件或 ORDER BY 清單中不能有非聚合欄位

mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'mydb.t.name'; this
is incompatible with sql_mode=only_full_group_by

如果沒有 GROUP BY，則只有單一分組，且對於該分組要選擇哪個 name 值是不確定的。這裡，如果 MySQL 選擇哪個 name 值不重要，也可以使用 ANY_VALUE()。

SELECT ANY_VALUE(name), MAX(age) FROM t;

ONLY_FULL_GROUP_BY 也會影響使用 DISTINCT 和 ORDER BY 的查詢處理。考慮一個表格 t，它有三個欄位 c1、c2 和 c3，並且包含以下這些列

假設我們執行以下查詢，預期結果會依 c3 排序

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

為了對結果排序，必須先消除重複項目。但要這麼做，我們應該保留第一列還是第三列？這個任意的選擇會影響保留的 c3 值，進而影響排序，使其也變得任意。為了避免這個問題，如果任何 ORDER BY 表達式不符合至少以下一個條件，則具有 DISTINCT 和 ORDER BY 的查詢會被視為無效而拒絕。

該表達式等於選取列表中的一個表達式
該表達式所引用且屬於查詢所選取表格的所有欄位，都是選取列表的元素

MySQL 對於標準 SQL 的另一個擴充，允許在 HAVING 子句中引用選取列表中的別名表達式。例如，以下查詢會傳回在 orders 表格中只出現一次的 name 值

SELECT name, COUNT(name) FROM orders
  GROUP BY name
  HAVING COUNT(name) = 1;

MySQL 擴充功能允許在 HAVING 子句中使用聚合欄位的別名

SELECT name, COUNT(name) AS c FROM orders
  GROUP BY name
  HAVING c = 1;

標準 SQL 只允許在 GROUP BY 子句中使用欄位表達式，因此像這樣的語句是無效的，因為 FLOOR(value/100) 是一個非欄位表達式

SELECT id, FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

MySQL 擴充標準 SQL 以允許在 GROUP BY 子句中使用非欄位表達式，並認為先前的語句有效。

標準 SQL 也不允許在 GROUP BY 子句中使用別名。MySQL 擴充標準 SQL 以允許別名，因此另一種寫入查詢的方式如下

SELECT id, FLOOR(value/100) AS val
  FROM tbl_name
  GROUP BY id, val;

別名 val 在 GROUP BY 子句中被視為欄位表達式。

在 GROUP BY 子句中存在非欄位表達式的情況下，MySQL 會識別該表達式與選取列表中的表達式之間的相等性。這表示在啟用 ONLY_FULL_GROUP_BY SQL 模式時，包含 GROUP BY id, FLOOR(value/100) 的查詢是有效的，因為相同的 FLOOR() 表達式也出現在選取列表中。但是，MySQL 不會嘗試識別對 GROUP BY 非欄位表達式的函數相依性，因此，即使第三個選取的表達式是 id 欄位和 GROUP BY 子句中的 FLOOR() 表達式的簡單公式，在啟用 ONLY_FULL_GROUP_BY 的情況下，以下查詢仍會無效

SELECT id, FLOOR(value/100), id+FLOOR(value/100)
  FROM tbl_name
  GROUP BY id, FLOOR(value/100);

一種解決方法是使用衍生表格

SELECT id, F, id+F
  FROM
    (SELECT id, FLOOR(value/100) AS F
     FROM tbl_name
     GROUP BY id, FLOOR(value/100)) AS dt;