In standard SQL, a query that includes a GROUP BY
clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY
clause. For example, this query is illegal in standard SQL because the 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;
For the query to be legal, the name
column must be omitted from the select list or named in the GROUP BY
clause.
MySQL extends the use of GROUP BY
so that the select list can refer to nonaggregated columns not named in the GROUP BY
clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY
are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY
clause. Sorting of the result set occurs after values have been chosen, and ORDER BY
does not affect which values within each group the server chooses.
A similar MySQL extension applies to the HAVING
clause. In standard SQL, a query that includes a GROUP BY
clause cannot refer to nonaggregated columns in the HAVING
clause that are not named in the GROUP BY
clause. A MySQL extension permits references to such columns to simplify calculations. This extension assumes that the nongrouped columns will have the same group-wise values. Otherwise, the result is indeterminate.
To disable the MySQL GROUP BY
extension, enable the ONLY_FULL_GROUP_BY
SQL mode. This enables standard SQL behavior: Columns not named in the GROUP BY
clause cannot be used in the select list or HAVING
clause unless enclosed in an aggregate function.
ONLY_FULL_GROUP_BY
also affects use of aliases in the HAVING
clauses. For example, the following query returnsname
values that occur only once in table orders
:
SELECT name, COUNT(name) FROM orders GROUP BY name HAVING COUNT(name) = 1;
MySQL extends this behavior to permit 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;
Enabling ONLY_FULL_GROUP_BY
disables this MySQL extension and a non-grouping field 'c' is used in HAVING clause
error occurs because the column c
in the HAVING
clause is not enclosed in an aggregate function (instead, it is an aggregate function).
The select list extension also applies to ORDER BY
. That is, you can refer to nonaggregated columns in the ORDER BY
clause that do not appear in the GROUP BY
clause. (However, as mentioned previously, ORDER BY
does not affect which values are chosen from nonaggregated columns; it only sorts them after they have been chosen.) This extension does not apply if the ONLY_FULL_GROUP_BY
SQL mode is enabled.
In some cases, you can use MIN()
and MAX()
to obtain a specific column value even if it is not unique. If the sort
column contains integers no larger than 6 digits, the following query gives the value of column
from the row containing the smallest sort
value:
SUBSTR(MIN(CONCAT(LPAD(sort,6,'0'),column)),7)
See Section 3.6.4, “The Rows Holding the Group-wise Maximum of a Certain Column”.
If you are trying to follow standard SQL, you cannot use expressions in GROUP BY
clauses. As a workaround, use an alias for the expression:
SELECT id, FLOOR(value/100) AS val
FROM tbl_name
GROUP BY id, val;
MySQL permits expressions in GROUP BY
clauses, so the alias is unnecessary:
SELECT id, FLOOR(value/100)
FROM tbl_name
GROUP BY id, FLOOR(value/100);
http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html
微信公众号: 架构师日常笔记 欢迎关注!
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· 本地部署 DeepSeek:小白也能轻松搞定!
· 如何给本地部署的DeepSeek投喂数据,让他更懂你
· 从 Windows Forms 到微服务的经验教训
· 李飞飞的50美金比肩DeepSeek把CEO忽悠瘸了,倒霉的却是程序员
· 超详细,DeepSeek 接入PyCharm实现AI编程!(支持本地部署DeepSeek及官方Dee
2013-11-24 线程安全