SQL-92 及更早版本不允许查询中选择列表、HAVING 条件或 ORDER BY 列表引用未在 GROUP BY 子句中列出的非聚合列。例如，在标准 SQL-92 中，这个查询是非法的，因为选择列表中的非聚合列 name 并未出现在 GROUP BY 中：SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause. For example, this query is illegal in standard SQL-92 because the nonaggregated name column in the select list does not appear in the 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 子句中命名。For the query to be legal in SQL-92, the name column must be omitted from the select list or named in the GROUP BY clause.

SQL:1999 及以后的版本如果可选功能 T301 允许非聚合列存在，并且这些列在功能上依赖于 GROUP BY 列，则查询是合法的：如果 name 与 custid 之间存在这种关系，则该查询是合法的。例如，如果 custid 是 customers 表的主键，那么情况就是如此。SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on GROUP BY columns: If such a relationship exists between name and custid, the query is legal. This would be the case, for example, were custid a primary key of customers.

MySQL 实现了函数依赖的检测。如果启用了 ONLY_FULL_GROUP_BY SQL 模式（默认启用），MySQL 会拒绝查询中选择列表、HAVING 条件或 ORDER BY 列表引用那些既未在 GROUP BY 子句中命名也不依赖于它们的非聚合列。MySQL implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

当启用 SQL ONLY_FULL_GROUP_BY 模式时，如果某一非聚合列限制为单一值，MySQL 也允许该列未在 GROUP BY 子句中命名，如以下示例所示：MySQL also permits a nonaggregate column not named in a GROUP BY clause when SQL ONLY_FULL_GROUP_BY mode is enabled, provided that this column is limited to a single value, as shown in the following example:

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 连接，如下所示：It is also possible to have more than one nonaggregate column in the SELECT list when employing ONLY_FULL_GROUP_BY. In this case, every such column must be limited to a single value in the WHERE clause, and all such limiting conditions must be joined by logical AND, as shown here:

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 不会影响服务器在每个组中选择哪个值。禁用ONLY_FULL_GROUP_BY主要在于你知道由于某些数据特性，每个组中 GROUP BY 没有列出的非聚合列的所有值都是相同的情况下才有用。If ONLY_FULL_GROUP_BY is disabled, a MySQL extension to the standard SQL use of GROUP BY permits the select list, HAVING condition, or ORDER BY list to refer to nonaggregated columns even if the columns are not functionally dependent on GROUP BY columns. This causes MySQL to accept the preceding query. In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are nondeterministic, which is probably not what you want. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Result set sorting occurs after values have been chosen, and ORDER BY does not affect which value within each group the server chooses. Disabling ONLY_FULL_GROUP_BY is useful primarily when you know that, due to some property of the data, all values in each nonaggregated column not named in the GROUP BY are the same for each group.

您可以通过使用 ANY_VALUE() 来引用非聚合列，而无需禁用 ONLY_FULL_GROUP_BY，从而实现同样的效果。You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.

以下讨论演示了函数依赖、当函数依赖缺失时 MySQL 产生的错误消息，以及在函数依赖缺失时让 MySQL 接受查询的方式。The following discussion demonstrates functional dependence, the error message MySQL produces when functional dependence is absent, and ways of causing MySQL to accept a query in the absence of functional dependence.

在启用 ONLY_FULL_GROUP_BY 时，此查询可能无效，因为查询列表中的非聚合列 address 未在 GROUP BY 子句中命名：This query might be invalid with ONLY_FULL_GROUP_BY enabled because the nonaggregated address column in the select list is not named in the GROUP BY clause:

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

如果 name 是表 t 的主键或者是唯一的 NOT NULL 列，则该查询是有效的。在这种情况下，MySQL 会识别所选列在功能上依赖于分组列。例如，如果 name 是主键，其值决定 address 的值，因为每个分组中只有一个主键值，因此只有一行。因此，分组中 address 值的选择没有随机性，也不需要拒绝该查询。The query is valid if name is a primary key of t or is a unique NOT NULL column. In such cases, MySQL recognizes that the selected column is functionally dependent on a grouping column. For example, if name is a primary key, its value determines the value of address because each group has only one value of the primary key and thus only one row. As a result, there is no randomness in the choice of address value in a group and no need to reject the query.

如果 name 不是 t 的主键或唯一的 NOT NULL 列，则查询无效。在这种情况下，无法推断出函数依赖性，会发生错误：The query is invalid if name is not a primary key of t or a unique NOT NULL column. In this case, no functional dependency can be inferred and an error occurs:

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() 函数：If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name. To tell MySQL to accept the query, you can use the ANY_VALUE() function:

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

或者，禁用 ONLY_FULL_GROUP_BY。Alternatively, disable ONLY_FULL_GROUP_BY.

然而，前面的示例相当简单。特别是，你不太可能仅根据单个主键列进行分组，因为每个分组只包含一行。有关展示更复杂查询中函数依赖的其他示例，请参见 第 14.19.4 节，“功能依赖检测”。The preceding example is quite simple, however. In particular, it is unlikely you would group on a single primary key column because every group would contain only one row. For additional examples demonstrating functional dependence in more complex queries, see Section 14.19.4, “Detection of Functional Dependence”.

如果查询包含聚合函数且没有 GROUP BY 子句，则在启用 ONLY_FULL_GROUP_BY 时，选择列表、HAVING 条件或 ORDER BY 列表中都不能包含非聚合列：If a query has aggregate functions and no GROUP BY clause, it cannot have nonaggregated columns in the select list, HAVING condition, or ORDER BY list with ONLY_FULL_GROUP_BY enabled:

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()：Without GROUP BY, there is a single group and it is nondeterministic which name value to choose for the group. Here, too, ANY_VALUE() can be used, if it is immaterial which name value MySQL chooses:

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

ONLY_FULL_GROUP_BY 还会影响使用 DISTINCT 和 ORDER BY 的查询的处理。考虑一个表 t，它有三列 c1、c2 和 c3，并且包含这些行：ONLY_FULL_GROUP_BY also affects handling of queries that use DISTINCT and ORDER BY. Consider the case of a table t with three columns c1, c2, and c3 that contains these rows:

c1 c2 c3
1  2  A
3  4  B
1  2  C

假设我们执行以下查询，期望结果按 c3 排序：Suppose that we execute the following query, expecting the results to be ordered by c3:

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

要对结果进行排序，必须首先消除重复项。但为此，我们应该保留第一行还是第三行呢？这个任意选择会影响保留的c3的值，而这又会影响排序，使其也变得任意。为防止此问题，如果任何ORDER BY表达式不满足以下任一条件，包含DISTINCT和ORDER BY的查询将被拒绝为无效：To order the result, duplicates must be eliminated first. But to do so, should we keep the first row or the third? This arbitrary choice influences the retained value of c3, which in turn influences ordering and makes it arbitrary as well. To prevent this problem, a query that has DISTINCT and ORDER BY is rejected as invalid if any ORDER BY expression does not satisfy at least one of these conditions:

MySQL 对标准 SQL 的另一个扩展允许在 HAVING 子句中引用选择列表中的别名表达式。例如，以下查询返回在表 orders 中只出现一次的 name 值：Another MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. For example, the following query returns name values that occur only once in table orders:

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

MySQL 扩展允许在聚合列的 HAVING 子句中使用别名：The MySQL extension permits the use of an alias in the HAVING clause for the aggregated column:

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

标准 SQL 仅允许在 GROUP BY 子句中使用列表达式，因此像这样的语句是无效的，因为 FLOOR(value/100) 是非列表达式：Standard SQL permits only column expressions in GROUP BY clauses, so a statement such as this is invalid because FLOOR(value/100) is a noncolumn expression:

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

MySQL 扩展了标准 SQL，允许在 GROUP BY 子句中使用非列表达式，并将前面的语句视为有效。MySQL extends standard SQL to permit noncolumn expressions in GROUP BY clauses and considers the preceding statement valid.

标准 SQL 也不允许在 GROUP BY 子句中使用别名。MySQL 扩展了标准 SQL 以允许使用别名，因此编写查询的另一种方法如下：Standard SQL also does not permit aliases in GROUP BY clauses. MySQL extends standard SQL to permit aliases, so another way to write the query is as follows:

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

别名 val 在 GROUP BY 子句中被视为列表达式。The alias val is considered a column expression in the GROUP BY clause.

子句中存在非列表达式时，MySQL 会识别该表达式与选择列表中表达式之间的相等性。这意味着在启用GROUP BYSQL 模式的情况下，包含ONLY_FULL_GROUP_BY的查询是有效的，因为相同的GROUP BY id, FLOOR(value/100)表达式出现在选择列表中。然而，MySQL 不会尝试识别对FLOOR()非列表达式的函数依赖，因此即使第三个选择的表达式是GROUP BY的简单公式，启用ONLY_FULL_GROUP_BY时以下查询也是无效的id column and the FLOOR() expression in the GROUP BY clause:In the presence of a noncolumn expression in the GROUP BY clause, MySQL recognizes equality between that expression and expressions in the select list. This means that with ONLY_FULL_GROUP_BY SQL mode enabled, the query containing GROUP BY id, FLOOR(value/100) is valid because that same FLOOR() expression occurs in the select list. However, MySQL does not try to recognize functional dependence on GROUP BY noncolumn expressions, so the following query is invalid with ONLY_FULL_GROUP_BY enabled, even though the third selected expression is a simple formula of the id column and the FLOOR() expression in the GROUP BY clause:

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

一种变通方法是使用派生表：A workaround is to use a derived table:

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