MySQL使用group by分組時報錯

MySQL使用group by分組時報錯

#1 環境

MySQL 5.7.20

1

#2 問題描述

輸入:

mysql> select * from SC group by Sid;

輸出結果:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'school_db.SC.Cid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#3 問題分析

ONLY_FULL_GROUP_BY的意思是：對於GROUP BY聚合操作，如果在SELECT中的列，沒有在GROUP BY中出現，那麼這個SQL是不合法的，因為列不在GROUP BY從句中，也就是說查出來的列必須在group by後面出現否則就會報錯，或者這個字段出現在聚合函數里面。

#4 解決

1. 查看sql_model參數命令

<code>SELECT @@GLOBAL.sql_mode;SELECT @@SESSION.sql_mode; /<code>

輸出:

<code>mysql> SELECT @@GLOBAL.sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@GLOBAL.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 |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql> SELECT @@SESSION.sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@SESSION.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 |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)/<code>

第一項默認開啟ONLY_FULL_GROUP_BY了,導致出現了錯誤,需要把它的默認關掉。

這兩個mode進行修改:

<code>SET GLOBAL sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';SET SESSION sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';/<code>

設置好之後,使用group by就不會出現之前的錯誤啦

閱讀更多 微科技分享 的文章

關鍵字: 分組 SQL 使用