Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘ry-vue.t_bas_ldar_device_info.dataID’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
查询
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
执行sql 去掉ONLY_FULL_GROUP_BY
set@@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
问题总结:@@global.sql_mode 严格设置sql的规则,根据异常提示了解到分组失败,ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作,如果在SELECT中的列、HAVING或者ORDER BY子句的列,没有在GROUP
BY中出现,那么这个SQL是不合法的。是可以理解的,因为不在 group by 的列查出来展示会有矛盾。
在5.7中默认启用,所以在实施5.6升级到5.7的过程需要注意:
sql版本问题;5.7.5之前,MySQL没有检测到功能依赖关系,默认情况下不启用ONLY_FULL_GROUP_BY
如果不可解决问题请前往这地看详细解释:https://www.cnblogs.com/piperck/p/9835695.html