MySql查询报错this is incompatible with sql_mode=only_full_group_by
错误示例
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘yiliaohaocai_new.a.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因
SQL 标准中不允许 SELECT 列表，HAVING 条件语句，或 ORDER BY 语句中出现 GROUP BY 中未列表的可聚合列。而 MySQL 中有一个状态 ONLY_FULL_GROUP_BY 来标识是否遵从这一标准，默认为开启状态。
解决方法
1. 临时关闭 ONLY_FULL_GROUP_BY
可以选择关掉 MySQL 的 ONLY_FULL_GROUP_BY 模式。
设置 sql_mode 来关闭。
首先查看变更前的 sql_mode：
select @@global.sql_mode
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));
再次查询 @@sql_mode 返回中应该已经没有该模式了。
注意：重启服务后失效
2. 配置文件关闭
Windows下打开my.ini文件 ，在mysqlld模块中的首行添加
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
linux下 vim /etc/mysql/conf.d/mysql.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
然后重启MySQL服务就可以了。
3. ANY_VALUE()
通过 ANY_VALUE() 来改造查询语句以避免报错。
MySQL提供了any_value(field)函数允许非分组字段的出现
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据
使用 ANY_VALUE() 包裹的值不会被检查，跳过该错误。
看报错信息，它提示你那个表的字段有问题，你就把哪个字段使用ANY_VALUE()框起来就可以
如下
Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘zhihuishequ.a.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
示例
SELECTANY_VALUE(a.id),d.`name`
FROM`biz_department_library` aLEFT JOIN sys_organization d on a.organization_id = d.id
GROUP BYd.id