多列子查询

当是单行多列的子查询时，主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝, >, <, >=, <=, <>）来进行比较；

当是多行多列子查询时，主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ALL,ANY）来进行比较。

案例

查看哪些国籍的教师年龄等于我司该国籍教师最大年龄

SELECT name, age
FROM teachers
WHERE (name, age)
IN(
    SELECT name,MAX(age)
    FROM teachers
    GROUP BY country
);

执行输出结果

mysql> SELECT name, age
    -> FROM teachers
    -> WHERE (name, age)
    -> IN(
    ->  SELECT name,MAX(age)
    ->     FROM teachers
    ->     GROUP BY country
    -> );
+------------------+-----+
| name             | age |
+------------------+-----+
| Eastern Heretic  |  20 |
| Northern Beggar  |  21 |
| Western Venom    |  28 |
| Southern Emperor |  21 |
+------------------+-----+
4 rows in set (0.01 sec)

练习题

使用课程表courses，查询每个教师授课学生人数最高的课程名称和上课人数

SELECT name,student_count
FROM courses
WHERE (teacher_id,student_count)
IN (
    SELECT teacher_id,MAX(student_count)
    FROM courses
    GROUP BY teacher_id
);

目标输出结果

mysql> SELECT name,student_count
    -> FROM courses
    -> WHERE (teacher_id,student_count)
    -> IN (
    ->  SELECT teacher_id,MAX(student_count)
    ->     FROM courses
    ->     GROUP BY teacher_id
    -> );
+-------------------------+---------------+
| name                    | student_count |
+-------------------------+---------------+
| Advanced Algorithms     |           880 |
| Artificial Intelligence |          1660 |
| Dynamic Programming     |          2000 |
+-------------------------+---------------+
3 rows in set (0.01 sec)