group by+having查询

          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从入门到精通