HAVING 子句中的子查询

HAVING子句的主要功能是对分组后的数据进行过滤，如果子查询在HAVING中表示要进行分组过滤，一般返回单行单列的数据

HAVING 子查询实例

查询所有教师授课的学生上课平均人数超过全部课程平均人数的教师姓名

SELECT name
FROM teachers
WHERE id in (
    SELECT teacher_id
    FROM courses
    GROUP BY teacher_id
    HAVING AVG(student_count) > ( 
        SELECT AVG(student_count)
        FROM courses
    )
);

执行输出结果

mysql> SELECT name
    -> FROM teachers
    -> WHERE id in (
    ->  SELECT teacher_id
    ->     FROM courses
    ->     GROUP BY teacher_id
    ->     HAVING AVG(student_count) > (
    ->          SELECT AVG(student_count)
    ->         FROM courses
    ->     )
    -> );
+-----------------+
| name            |
+-----------------+
| Eastern Heretic |
| Western Venom   |
+-----------------+
2 rows in set (0.01 sec)

练习题

查询不同国籍教师的平均年龄大于所有教师平均年龄的的教师姓名