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;