1.问题发生
问题
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'reporter.reporter_status.N_APP_CODE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
字面意思
this is incompatible with sql_mode=only_full_group_by
这与sql_mode=only_full_group_by不兼容
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'reporter.reporter_status.N_APP_CODE' which is not functionally dependent on columns in GROUP BY clause
选择列表的表达式#2不在GROUP BY子句中,并且包含非聚合列的reporter_status。N_APP_CODE',它在功能上不依赖于GROUP BY子句中的列;
问题发生的语句
select id,count(*),an
from tb4
group by id
2.问题解决
2.1问题分析
数据源
问题原因:id为1的分组里面出现了两个an(1和4)。所以出现了选择的问题。
2.2 解决方案:
方案1. 为查找出的发生碰撞的字段加上any_value函数,于是就成了下面这种
select id,count(*),any_value(an)
from tb4
group by id
结果
方案2 设置 sql_mode
2.1 获取 @@sql_mode
select @@sql_mode
结果如下,这里的ONLY_FULL_GROUP_BY就是要删除的模式
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
2.2 设置 @@sql_mode(有两种方法)
长效
SET GLOBAL sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
临时有效 (重启后会失效)
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
方案3.设置配置文件(在my.ini文件中追加如下配置)
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION