mysql5.7 group by 的两个问题

最新推荐文章于 2025-03-22 12:20:30 发布

都市放猪

最新推荐文章于 2025-03-22 12:20:30 发布

阅读量407

点赞数

分类专栏： mysql

本文链接：https://blog.youkuaiyun.com/wq57885/article/details/82422954

版权

mysql 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何解决MySQL中因sql_mode设置为only_full_group_by导致的查询错误，并提供了一个具体的例子说明如何通过修改配置文件及使用LIMIT来确保查询结果的正确性。

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

一：SELECT list is not in GROUP BY clause and contains nonaggregated column ‘news.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by.

vi /etc/my.cnf

[mysqld]下加入

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

二：group by 和 order by 配合失效，下面这种写法在5.7版本是无法获得最新的一列

SELECT * FROM `lf_qingbao` `q` LEFT JOIN (SELECT *FROM lf_qingbao_zhuangtai order by id desc ) `z` ON `q`.`id`=`z`.`qingbaoid` WHERE `yonghuid` = 44165 GROUP BY `q`.`id`

只需在子sql里加入limit 10000000000

也就是

SELECT * FROM `lf_qingbao` `q` LEFT JOIN (SELECT *FROM lf_qingbao_zhuangtai order by id desc limit 10000000000) `z` ON `q`.`id`=`z`.`qingbaoid` WHERE `yonghuid` = 44165 GROUP BY `q`.`id`