mysql5.7使用groupby出现1055Expression #2 of SELECT list is not in GROUP BY clause 错误_42000][1055] expression #2 of select list is not i

今天想实现一个查看某一个类别的其中一项记录时，使用groupby分组，在mysql5.7出现，如下错误

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

在mysql中使用select @@sql_mode;查询：可以看到有ONLY_FULL_GROUP_BY

select @@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

解决方法：

第一步：在mysql命令行下执行以下语句： 

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

第二步：在mysql的配置文件中（linux下/etc/my.cnf或者/etc/mysql/my.cnf的[mysqld]下加入以下内容：

[mysqld]  
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服务

[root@hadoop01 mysql-5.7]# systemctl restart mysqld.service

再次查询

select @@sql_mode;
 
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION