使用 GROUP BY 语句出现错误:

[SQL]SELECT
    id,sq_resident_id
FROM
    sq_resident_family_info
GROUP BY sq_resident_id

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

可以看到我的SQL语句是

SELECT
    id,sq_resident_id
FROM
    sq_resident_family_info
GROUP BY sq_resident_id

查询资料得知,执行命令

select @@global.sql_mode;

结果为

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

含有 ONLY_FULL_GROUP_BY,说明设置了此模式。mysql5.7.5后,ONLY_FULL_GROUP_BY 默认为真,那么此时select中的字段必须出现在group by中。
那么在我的SQL语句中selectidsq_resident_id,那么这两个字段都得在GROUP_BY

SELECT
    id,sq_resident_id
FROM
    sq_resident_family_info
GROUP BY sq_resident_id, id

这样执行就不会报错了,但是我不需要分组id字段,去掉即可,然后我要统计sq_resident_id字段的重复值

SELECT
    count(id), sq_resident_id
FROM
    sq_resident_family_info
GROUP BY
    sq_resident_id

总结,如果 sql_mode 设置了 ONLY_FULL_GROUP_BY ,那么select字段要出现在GROUP_BY

如未注明,均为原创,转载请注明来自Peak Xin's Blog

No Comments »