原因
《对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中。简而言之,就是SELECT后面接的列必须被GROUP BY后面接的列所包含。》
如:
select a,b from table group by a,b,c; (正确)
select a,b,c from table group by a,b; (错误)
解决
- 方法1 取消only_full_group_by
MySQL 5.7.5版本之前需要设置数据库取消only_full_group_by
sql中敲命令,可在Navicat中查询更方便
复制查出的结果去掉(only_full_group_by),如果前面没有就直接复制
mysql> select @@GLOBAL.sql_mode; #查询出后去掉ONLY_FULL_GROUP_BY把结果粘贴进去,注意逗号 mysql> set @@GLOBAL.sql_mode = 查出的字段值
去除方法2 直接替换SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
以下同上,没查出就不用改,一般只修改第一个即可,查询出来后把查询结果中的only_full_group_by去掉mysql> select @SESSION.sql_mode; mysql> set SESSION.sql_mode = 查出的字段值
以上方法在每次启动数据库的时候都需要重新配置,一劳永逸的方式是修改MYSQL的配置文件中sql_mode的配置以下是步骤
进入etc目录编辑my.cnf,添加查询到的配置cd /etc
[root@localhost etc]# vim my.cnf sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION :wq退出 #配置完后需要重启mysql systemctl restart mysqld
- 方法2 不做修改,使用any_value(field)函数
使用any_value(field)函数。
any_value(field)介绍:
MySQL提供了any_value(field)函数允许非分组字段的出现
any_value()会选择被分到同一组的数据里第一条数据的指定列值作为返回数据用法:
SELECT any_value ( s.name ) name), any_value ( s.id ) id FROM result r LEFT JOIN student s ON s.id = r.id WHERE c.r= '112233' GROUP BY s.type
【注意】:MySQL 8.0开始 group by 默认是没有排序的