MYSQL使用GROUP BY 时报错only_full_group_by的解决方法

最新推荐文章于 2024-09-29 16:12:54 发布

农民奔小康

最新推荐文章于 2024-09-29 16:12:54 发布

阅读量324

点赞数

本文链接：https://blog.csdn.net/qq_38091831/article/details/103145994

版权

mysql GROUP BY 时报以下错误：

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pro_data.sy_epwk.grade' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解决方法：

1、查看sql_model参数命令：select @@global.sql_mode;

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

2、把查询结果中的ONLY_FULL_GROUP_BY去掉，执行下面对应的语句。

1）改变了全局sql_mode，对于新建的数据库有效：set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'
2）已存在的数据库，则需要在对应的数据下执行：set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';