在标准 SQL 中,包含GROUP
BY
子句的查询不能引用选择列表中未在GROUP
BY
子句中命名的非聚合列。例如,此查询在标准 SQL 中是非法的,因为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;
为了使查询合法,该name
列必须从选择列表中省略或在
GROUP BY
子句中命名。
MySQL 扩展了标准 SQL 的使用,GROUP
BY
因此选择列表可以引用GROUP BY
子句中未命名的非聚合列。这意味着上述查询在 MySQL 中是合法的。您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。但是,这主要在GROUP BY
每个组中未命名的每个非聚合列中的所有值都相同时有用。服务器可以自由地从每个组中选择任何值,因此除非它们相同,否则所选择的值是不确定的。此外,从每个组中选择值不会受到添加ORDER
BY
子句的影响。结果集排序发生在选择值之后,并且ORDER BY
不影响服务器选择每个组中的哪些值。
类似的 MySQL 扩展适用于该
HAVING
子句。在标准 SQL 中,查询不能引用
HAVING
子句中未在子句中命名的非
聚合列GROUP BY
。为了简化计算,MySQL 扩展允许引用此类列。此扩展假定未分组的列具有相同的分组值。否则,结果是不确定的。
要禁用 MySQLGROUP BY
扩展并启用标准 SQL 行为,请启用
ONLY_FULL_GROUP_BY
SQL 模式。在这种情况下,未在子句中命名的列不能GROUP
BY
在选择列表或
HAVING
子句中使用,除非包含在聚合函数中。
选择列表扩展也适用于ORDER
BY
. 也就是说,您可以在ORDER BY
子句中引用未出现在
GROUP BY
子句中的非聚合列。(但是,如前所述,ORDER BY
不影响从非聚合列中选择哪些值;它仅在选择它们之后对其进行排序。)如果ONLY_FULL_GROUP_BY
启用了 SQL 模式,则此扩展不适用。
如果查询具有聚合函数但没有子句,则它在选择列表、条件或
启用
的列表
GROUP
BY
中不能有非聚合列:HAVING
ORDER BY
ONLY_FULL_GROUP_BY
mysql> SELECT name, MAX(age) FROM t;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause
如果没有GROUP BY
,则只有一个组,并且不确定name
为该组选择哪个值。
标准 SQL 的另一个 MySQL 扩展允许在HAVING
子句中引用选择列表中的别名表达式。启用
ONLY_FULL_GROUP_BY
可以防止这种情况。例如,以下查询返回
name
在表中只出现一次的值
orders
;无论是否ONLY_FULL_GROUP_BY
启用,查询都会被接受:
SELECT name, COUNT(name) FROM orders
GROUP BY name
HAVING COUNT(name) = 1;
ONLY_FULL_GROUP_BY
仅当禁用
时才接受以下查询
。
SELECT name, COUNT(name) AS c FROM orders
GROUP BY name
HAVING c = 1;
如果您尝试遵循标准 SQL,则只能在GROUP BY
子句中使用列表达式。解决方法是为表达式使用别名:
SELECT id, FLOOR(value/100) AS val
FROM tbl_name
GROUP BY id, val;
MySQL 允许在子句中使用非列表达式GROUP
BY
,因此不需要别名:
SELECT id, FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);