报错:In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column

sql语句报错内容:

Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sw_promanage.a.id'; this is incompatible with sql_mode=only_full_group_by

报错原因:

MySQL版本问题,有的版本默认开启了ONLY_FULL_GROUP_BY,所以导致了一些SQL无法正常执行,其实是因为group by 之后,返回的一些数据是不确定的,所以才会出现这个错误。

解决方法(两种):

  • (1)使用ANY_VALUE(column_name)。

例如:
错误代码:

SELECT 
	a.id AS "id",
	a.project_id AS "projectId",
	sum(a.reward_money) AS "rewardMoney",
	sum(a.punish_money) AS "punishMoney"
FROM pm_reward_punishment_plan a
where a.project_id = 'xxxxxxxx'

修改后代码:

SELECT 
	ANY_VALUE(a.id) AS "id",
	a.project_id AS "projectId",
	sum(a.reward_money) AS "rewardMoney",
	sum(a.punish_money) AS "punishMoney"
FROM pm_reward_punishment_plan a
where a.project_id = 'xxxxxxxx'
  • (2)设置数据库,关闭ONLY_FULL_GROUP_BY。

编辑my.cnf文件,找到sql-mode的位置,去掉ONLY_FULL_GROUP_BY,重启MySQL服务;
如my.cnf中没有sql-mode,需要加入以下:
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服务。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页