数据库错误ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY

最新推荐文章于 2023-07-09 11:16:05 发布

原创最新推荐文章于 2023-07-09 11:16:05 发布

· 1.2k 阅读

0 ·

版权

文章标签：

#mysql

这篇博客介绍了如何处理MySQL在使用GROUP BY时遇到的ONLY_FULL_GROUP_BY错误。通过展示查询语句和调整SQL模式，作者展示了如何禁用该模式以允许不完全分组的排序。具体操作包括查看当前SQL模式，设置SQL模式为不包含ONLY_FULL_GROUP_BY，然后成功运行查询。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用的这篇文章的方法解决滴：
https://blog.youkuaiyun.com/jgj0129/article/details/53420574

这里就是自己再记录一下。加粗的就是用到的语句。

mysql> select dept_name,avg(tot_cred) from student
-> group by dept_name having avg(tot_cred)>80 order by(tot_cred) desc;
ERROR 1055 (42000): Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘mylab.student.tot_cred’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> show variables like ‘%sql_mode%’;
±--------------±----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±----------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
±--------------±----------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.02 sec)

mysql> set sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like ‘%sql_mode%’;
±--------------±---------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±---------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
±--------------±---------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)