HAVING

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

HAVING 实例

请根据课程表 courses 和教师表 teachers，查询课程学生少于 3000 人的任课教师姓名及其授课人数，分别用 name 和 student_count 表示；

对于没有任课的老师，授课人数应当为 0；

查询结果应当按照人数升序排列，如果上课人数相同，则按照教师姓名升序排列

SELECT T.name as name, IFNULL(SUM(C.student_count),0) as student_count
FROM courses C RIGHT JOIN teachers T
ON C.teacher_id = T.id
GROUP BY T.id
HAVING student_count<3000
ORDER BY student_count, name;

执行输出结果

mysql> SELECT T.name as name, IFNULL(SUM(C.student_count),0) as student_count
    -> FROM courses C RIGHT JOIN teachers T
    -> ON C.teacher_id = T.id
    -> GROUP BY T.id
    -> HAVING student_count<3000
    -> ORDER BY student_count, name;
+------------------+---------------+
| name             | student_count |
+------------------+---------------+
| Linghu Chong     |             0 |
| Northern Beggar  |             0 |
| Southern Emperor |          1520 |
+------------------+---------------+
3 rows in set (0.00 sec)

可以看到学生上课总人数少于3000的教师有三人，上课学生数量如上所示。

学生上课人数都为0的教师，按姓名排序。

HAVING 总结

学习完上述的HAVING案例后，我们可以总结

语法

SELECT   column_name, 
         aggregate_function(column_name) 
FROM     table_name 
WHERE    column_name operator value 
GROUP BY column_name 
HAVING   aggregate_function(column_name) operator value;

练习题：HAVING

题目描述：我们希望查询按照教师国籍给所有课程分类，并统计不同国籍教师的学生总人数大于3000的国家，返回国籍和学生人数，结果按照学生人数降序排列，如果人数相同，按照国家名称升序排列

SELECT T.country country, SUM(student_count) student_count
FROM courses C LEFT JOIN teachers T
ON C.teacher_id = T.id
GROUP BY T.country
HAVING student_count>3000
ORDER BY student_count DESC,country;

目标输出结果

mysql> SELECT T.country country, SUM(student_count) student_count
    -> FROM courses C LEFT JOIN teachers T
    -> ON C.teacher_id = T.id
    -> GROUP BY T.country
    -> HAVING student_count>3000
    -> ORDER BY student_count DESC,country;
+---------+---------------+
| country | student_count |
+---------+---------------+
| USA     |          4570 |
| UK      |          3200 |
+---------+---------------+
2 rows in set (0.00 sec)