MySQL中GROUP BY分组多字段以及HAVING的使用

SQL分组GROUP BY多个字段的情况：

GROUP BY在需要数据统计时经常用到，一般与聚合函数(（COUNT, SUM, AVG, MIN, MAX）配合使用，他是可以使用多个字段的，下面看看GROUP BY多个字段的情况。

这里有一个测试数据表：

+----+------+-----+------------+-----------+
| id | city | age | first_name | last_name |
+----+------+-----+------------+-----------+
|  1 | LA   |  34 | Lebron     | James     |
|  2 | LA   |  26 | Anthony    | Davis     |
|  3 | NY   |  36 | Carmelo    | Anthony   |    
|  4 | NY   |  26 | James      | Harden    |
|  5 | SH   |  34 | Yao        | Ming      |
|  6 | GS   |  26 | Stephen    | Curry     |
|  7 | GS   |  26 | Klay       | Thompson  |
+----+------+-----+------------+-----------+

只以age分组：

SELECT `age`, `city`, COUNT(id) AS `count` FROM `nba_star` GROUP BY `age`;

array ( 
    array ( 'age' => '26', 'city' => 'LA', 'count' => '4', ), 
    array ( 'age' => '34', 'city' => 'LA', 'count' => '2', ), 
    array ( 'age' => '36', 'city' => 'NY', 'count' => '1', ), 
)

只以city分组

SELECT `age`, `city`, COUNT(id) AS `count` FROM `nba_star` GROUP BY `city`;

array ( 
    array ( 'age' => '26', 'city' => 'GS', 'count' => '2', ), 
    array ( 'age' => '34', 'city' => 'LA', 'count' => '2', ),
    array ( 'age' => '36', 'city' => 'NY', 'count' => '2', ), 
    array ( 'age' => '34', 'city' => 'SH', 'count' => '1', ), 
)

以city和age分组

SELECT `age`, `city`, COUNT(id) AS `count` FROM `nba_star` GROUP BY `city`, `age`;

array ( 
    array ( 'age' => '26', 'city' => 'GS', 'count' => '2', ), 
    array ( 'age' => '26', 'city' => 'LA', 'count' => '1', ), 
    array ( 'age' => '34', 'city' => 'LA', 'count' => '1', ), 
    array ( 'age' => '26', 'city' => 'NY', 'count' => '1', ), 
    array ( 'age' => '36', 'city' => 'NY', 'count' => '1', ), 
    array ( 'age' => '34', 'city' => 'SH', 'count' => '1', ), 
)

以age和city分组

SELECT `age`, `city`, COUNT(id) AS `count` FROM `nba_star` GROUP BY `age`, `city`;

array ( 
    array ( 'age' => '26', 'city' => 'GS', 'count' => '2', ), 
    array ( 'age' => '26', 'city' => 'LA', 'count' => '1', ), 
    array ( 'age' => '26', 'city' => 'NY', 'count' => '1', ), 
    array ( 'age' => '34', 'city' => 'LA', 'count' => '1', ), 
    array ( 'age' => '34', 'city' => 'SH', 'count' => '1', ), 
    array ( 'age' => '36', 'city' => 'NY', 'count' => '1', ), 
)

通过上面的示例可以看出来，当使用多个字段进行分组的时候，是将所有具有相同age字段值和city字段值的记录放到一个分组里之后来统计的，被分组字段还会自动按顺序被排序。

GROUP BY分组配合HAVING使用

在使用聚合函数以后，常用的WHERE不能对聚合函数进行筛选，这时候我们就需要HAVING了，它一般就是用来过滤GROUP BY查询产生的数据的。

如我们有一张测试表，记录的是一些NBA球员的赛季总得分：

+----+------+-------+------------+-----------+
| id | year | score | first_name | last_name |
+----+------+-------+------------+-----------+
|  1 | 2019 |  1505 | Lebron     | James     |
|  2 | 2019 |  1452 | Anthony    | Davis     |
|  3 | 2019 |   134 | Carmelo    | Anthony   |
|  4 | 2019 |  2818 | James      | Harden    |
|  5 | 2018 |  1792 | Kevin      | Durant    |
|  6 | 2019 |  1881 | Stephen    | Curry     |
|  7 | 2019 |  1680 | Klay       | Thompson  |
|  8 | 2019 |  2027 | Kevin      | Durant    |
|  9 | 2018 |  2191 | James      | Harden    |
| 10 | 2018 |  2251 | Lebron     | James     |
| 11 | 2018 |  1261 | Carmelo    | Anthony   |
| 12 | 2018 |  1346 | Stephen    | Curry     |
+----+------+-------+------------+-----------+

我们需要查询出他们的赛季总得分的平均分，并且筛选出平均分超过1500分的球员。

SELECT `last_name`, AVG(score) AS `avg_score` FROM `nba_score` GROUP BY `last_name` HAVING `avg_score` > 1500;

array ( 
    array ( 'last_name' => 'Curry', 'avg_score' => '1613.5000', ), 
    array ( 'last_name' => 'Durant', 'avg_score' => '1909.5000', ), 
    array ( 'last_name' => 'Harden', 'avg_score' => '2504.5000', ), 
    array ( 'last_name' => 'James', 'avg_score' => '1878.0000', ), 
    array ( 'last_name' => 'Thompson', 'avg_score' => '1680.0000', ), 
)

这就是HAVING配合GROUP BY进行数据筛选的用法了。