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

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / 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 接受查詢的方法。

啟用 ONLY_FULL_GROUP_BY 後，此查詢可能無效，因為選取清單中的非聚合 address 欄位未在 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 的查詢處理。考慮一個包含三個欄位 c1、c2 和 c3 的表格 t，其中包含下列列：

假設我們執行以下查詢，期望結果按照 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;