没有 GROUP BY 子句的 MySQL 聚合函数 [英] MySQL Aggregate Functions without GROUP BY clause

问题描述

在 MySQL 中，我观察到在 SELECT 列表中使用 AGGREGATE FUNCTION 的语句会被执行，尽管没有 GROUP BY 子句.如果我们这样做，其他 RDBMS 产品(如 SQL Server)会抛出错误.

In MySQL, I observed that a statement which uses an AGGREGATE FUNCTION in SELECT list gets executed though there is no GROUP BY clause. Other RDBMS products like SQL Server throw an error if we do so.

例如，SELECT col1,col2,sum(col3) FROM tbl1; 执行时没有任何错误，并返回 col1,col2 的第一行值和 col3 的所有值的总和.上面查询的结果是一行.

For example, SELECT col1,col2,sum(col3) FROM tbl1; gets executed without any error and returns the first row values of col1,col2 and sum of all values of col3. The result of the above query is a single row.

谁能告诉为什么 MySQL 会发生这种情况?

Can anyone please tell why does this happen with MySQL?

提前致谢！！

推荐答案

这是设计使然 - 它是 MySQL 允许的标准的众多扩展之一.

It's by design - it's one of many extensions to the standard that MySQL permits.

对于像 SELECT name, MAX(age) FROM t; 这样的查询，参考文档说:

For a query like SELECT name, MAX(age) FROM t; the reference docs says that:

没有GROUP BY，只有一个组，是不确定的为组选择哪个名称值

Without GROUP BY, there is a single group and it is indeterminate which name value to choose for the group

参见文档关于 group by处理更多信息.

See the documentation on group by handling for more information.

设置 ONLY_FULL_GROUP_BY 控制此行为，请参阅 5.1.7 服务器 SQL 模式启用此功能将禁止使用缺少 group by 语句的聚合函数的查询，并且在 MySQL 5.7.5 版中默认启用.

The setting ONLY_FULL_GROUP_BY controls this behavior, see 5.1.7 Server SQL Modes enabling this would disallow a query with an aggregate function lacking a group by statement and it's enabled by default from MySQL version 5.7.5.

这篇关于没有 GROUP BY 子句的 MySQL 聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！