MySQL查询统计报错this is incompatible with sql_mode=only_full_group_by

1.问题描述

sql查询统计异常

SELECT 	t.username,t.police_no,SUM(t.totalflytime) AS totalTimeFROM trackname t
WHERE t.delete_state = 1 AND t.police_no IS NOT NULL
GROUP BY t.police_noExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'feidun.t.username' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2.分析原因

字面意思理解是sql_model=only_full_group_by限制了，导致在以往MYSQL版本中能正常查询的SQL，在5.7不能用了
参考文档：
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-setting
文档指出：ONLY_FULL_GROUP_BY的设定，将不允许查询字段包括非聚集列

3.解决办法

(1)升级mysql版本
(2)在group by补全你要查询的非聚集列，也就是如果select查询的列[非函数]，都应该在group by后，否则就会报错
sql正确实例：

SELECT 	t.username,t.police_no,SUM(t.totalflytime) AS totalTimeFROM 	test t
WHERE t.delete_state = 1 AND t.police_no IS NOT NULL
GROUP BY t.police_no,t.username