Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column【bug】解决方案

问题场景：

select stuid from student_table GROUP BY stuAccount HAVING count(stuAccount)>1;

运行完这条sql语句后mysql就报了这个错误。

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column…

---

解决方案： 1.对于已经存在的数据库

执行下面的sql语句即可（没有效果重启数据库服务）

SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

2.对于新建的数据库

执行下面的sql语句即可（没有效果重启数据库服务）

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

3.修改SQL语句

我在stack overflow上找到了高赞回答。

When MySQL’s only_full_group_by mode is turned on, it means that strict ANSI SQL rules will apply when using GROUP BY. With regard to your query, this means that if you GROUP BY of the proof_type column, then you can only select two things:

• the proof_type column,
• or aggregates of any other column

简单翻译过来就是在only_full_group_by这种模式下，使用GROUP BY只能查询proof_type列和使用聚合函数的列。

聚合函数如MIN(), MAX(), or AVG()。

于是我在stuid属性上加max()就可以了。

select max(stuid) from student_table GROUP BY stuAccount HAVING count(stuAccount)>1;

---