group by order by 同时使用_Mysql 使用 Group by 之Error 1055 之坑

问题：mysql版本5.7，使用group by 分组查询遇到

Error : Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'mysql.course.cname' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by
           

问题起因：

ONLY_FUll_GROUP_BY的意思是：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY语句中，也就是说查出来的列必须是GROUP BY之后的字段，或者这个字段出现在聚合函数里面。

探索原因：

这个错误信息和sql_mode中的”only_full_group_by”有关，是Mysql5.7 group by新特性，在5.7版本中only_full_group_by这个模式是默认开启的

解决办法：

①通过mysql的any_value()函数

#SELECT语句中any_value使用
select t.cid,any_value(cname)as 课程名称,max(score)as 最高分,
min(score)as 最低分,avg(score) as 平均分,
sum(及格)/count(sid) as 及格率,
sum(中等)/count(sid) as 中等率,
sum(优良)/count(sid) as 优良率,
sum(优秀)/count(sid) as 优秀率
from
(select *,case when score>=60 then 1 else 0 end as 及格,
case when score>=70 and score<80 then 1 else 0 end as 中等,
case when score>=80 and score<90 then 1 else 0 end as 优良,
case when score>=90 then 1 else 0 end as 优秀
from SC)t
left join course on t.cid=course.cid  #连接课程表得到对应课程名称
group by cid
order by cid
           

②修改mysql配置my.cnf(windows下面是my.ini)，删除only_full_group_by属性，通过命令行进行修改

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

把查询出来的sql_mode中的only_full_group_by删除重新set sql_model = xxxx即可，然后重新启动mysql服务

查询mysql服务器版本

select @@version;
           

查看sql_mode

SELECT @@sql_mode;
select @@GLOBAL.sql_mode;
select @@SESSION.sql_mode;
           

修改sql_mode

SET GLOBAL sql_mode = '';
           

或

set @@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 5.7默认的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, and NO_ENGINE_SUBSTITUTION

group by 正确用法

select 选取分组中的列 + 聚合函数 from 表名称 group by 分组的列

从语法格式来看，是现有分组，在确定检索的列，检索的列只能是参加分组了的列。

所以问题中，group by 后的a,b,c是先确定的，而select 后面的字段是可变的

正确的语法：

select a,b,c from table_name group by a,b,c,d;
select a,b from table_name group by a,b,c;
select a,max(a) from table_name group by a,b,c
           

以下是错误的：

select a,b,c from table_name group by a,b;
select a,b,c from table_name group by a;