? ? ? ? ? group by：把數據分為多個邏輯組，并對每個邏輯進行操作。

? ? ? ? ? having：用來過濾分組。

mysql> select * from friut; +------+------+--------+---------+ | f_id | s_id | f_name | f_price | +------+------+--------+---------+ | a | 102 | apple | 5 | | b | 103 | orange | 9 | | c | 104 | melon | 2 | | d | 105 | banana | 8 | | e | 102 | grape | 2 | | f | 104 | mango | 8 | +------+------+--------+---------+ 6 rows in set (0.00 sec) mysql> select * from friut group by s_id; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mapan.friut.f_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我直接查詢并通過s_id進行分組直接報錯。這個查詢語句分組了，但是還是直接顯示所有，有些字段分組不能顯示，所以報錯。

mysql> select s_id from friut group by s_id; +------+ | s_id | +------+ | 102 | | 103 | | 104 | | 105 | +------+ 4 rows in set (0.00 sec)

這次執行成功了，我只選取分組之后s_id結果。

mysql> select s_id,count(s_id) from friut group by s_id; +------+-------------+ | s_id | count(s_id) | +------+-------------+ | 102 | 2 | | 103 | 1 | | 104 | 2 | | 105 | 1 | +------+-------------+ 4 rows in set (0.00 sec)mysql>

這次不僅分組了，還計算出了每組對應s_id的數量。group by通常與MAX(),MIN(),COUNT(),SUM(),AVG()這些函數一起使用。

mysql> select s_id,count(s_id) as total from friut group by s_id having total>1 ; +------+-------+ | s_id | total | +------+-------+ | 102 | 2 | | 104 | 2 | +------+-------+ 2 rows in set (0.00 sec)mysql>

加上having過濾條件。很簡單，但也很容易忘記。

?

?

?

參考資料：mysql從入門到精通

?

?

總結

以上是生活随笔為你收集整理的group by+having查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。