关于mysql5.7版本以上group by 报错的问题说明（不改mysql配置）

当我在本地环境测试的时候（mysql5.6）结果如下图：

SELECT mid,MAX(historyid) historyid,MAX(createtime) max_create_time,MIN(createtime) as min_createtime,createtime FROM history GROUP BY mid ORDER BY historyid DESC limit 0,20

create_time会有一个默认值，这个默认值取的是最小值

同样的sql在（mysql5.7）环境下运行，报错

[Err] 1055 - Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'newweb.history.createtime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

这句sql去掉createtime之后就正常工作了。因为这个值有多个，数据库不知道给我们返回哪一条数据。

 

SELECT mid,MAX(historyid) historyid,MAX(createtime) max_create_time,MIN(createtime) as min_createtime FROM history GROUP BY mid ORDER BY historyid DESC limit 0,20

说明mysql5.7使用groupby的时候不会给出默认值，我们查询的时候需要我们指定一个最大值或者最小值，精确到某个值之后就能使用group by了