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子句中命名该列。

SQL：1999和更高版本允许这些非聚合，如果它们在功能上依赖于GROUP BY列，则每个可选功能T301都允许此类非聚合：如果name和之间存在这种关系custid，则查询合法。例如，如果是custid的主键就是这种情况customers。

MySQL实现对功能依赖性的检测。如果ONLY_FULL_GROUP_BY启用了 SQL模式（默认情况下为SQL模式），则MySQL拒绝查询其选择列表，HAVING条件或ORDER BY列表引用GROUP BY未在该子句中命名或在功能上不依赖于它们的未聚合列的查询。

启用GROUP BYSQL ONLY_FULL_GROUP_BY模式时， MySQL还允许在子句中未命名的非聚合列，但前提是该列限于单个值，如以下示例所示：

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 	|
+------	+--------	+

也可以在有一个以上的非聚合列SELECT使用时列表ONLY_FULL_GROUP_BY。在这种情况下，必须在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被禁用，则对标准SQL使用的MySQL扩展GROUP BY允许选择列表，HAVING条件或ORDER BY列表引用未聚合的列，即使这些列在功能上不依赖于GROUP BY列。这导致MySQL接受前面的查询。在这种情况下，服务器可以从每个组中自由选择任何值，因此，除非它们相同，否则选择的值是不确定的，这可能不是您想要的。此外，通过添加ORDER BY子句不能影响从每个组中选择值。选择值后进行结果集排序，然后ORDER BY不会影响服务器在每个组中选择哪个值。禁用ONLY_FULL_GROUP_BY主要有用，因为您知道由于数据的某些属性，每个未聚合列中未命名的所有值GROUP BY对于每个组都是相同的。

ONLY_FULL_GROUP_BY通过使用ANY_VALUE()引用未聚合的列，可以在不禁用的情况下实现相同的效果。

下面的讨论演示了功能依赖性，不存在功能依赖性时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。

但是，前面的示例非常简单。特别是，不太可能将您分组在一个主键列上，因为每个组将只包含一行。有关在更复杂的查询中展示功能依赖关系的其他示例，请参见“检测功能依赖关系”。

如果查询中聚合函数和没有GROUP BY条款，也不能有非聚合列在选择列表，HAVING条件，或ORDER BY清单，ONLY_FULL_GROUP_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为该组选择哪个值。ANY_VALUE()如果nameMySQL选择哪个值无关紧要，也可以在这里使用：

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

ONLY_FULL_GROUP_BY也影响使用DISTINCT和的查询的处理ORDER BY。考虑表的情况下，t有三列c1，c2以及c3包含这些行：

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

假设我们执行以下查询，期望结果按以下顺序排序c3：

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

为了订购结果，必须首先消除重复。但是要这样做，我们应该保留第一行还是第三行？这种任意选择会影响的保留值c3，进而影响顺序并使其也具有任意性。为避免此问题，如果任何表达式不满足以下条件之一，则具有DISTINCT且ORDER BY被拒绝为无效的查询ORDER BY：

• 该表达式等于选择列表中的一个
• 表达式引用的并属于查询的所选表的所有列都是选择列表的元素

对标准SQL的另一个MySQL扩展允许在HAVING子句中引用选择列表中的别名表达式。例如，以下查询返回name在表中仅出现一次的值orders：

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列表达式的功能依赖性，因此ONLY_FULL_GROUP_BY即使第三个选择的表达式是该id列和子句中的FLOOR()表达式的简单公式，以下查询在启用后也是无效的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;