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

问题描述

在MySQL中，我观察到尽管没有GROUP BY子句，但在SELECT列表中使用AGGREGATE FUNCTION的语句被执行了.如果这样做的话，其他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

关于分组依据，请参见文档处理以获取更多信息.

See the documentation on group by handling for more information.

设置ONLY_FULL_GROUP_BY控制此行为，请参见 5.1.7 Server SQL Modes 启用此功能将不允许使用缺少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屋！