内联视图子查询

内联视图子查询实际上就是将查询的结果集作为一个查询表，继续进行查询操作

实例

从教师表teachers中查询国籍为'USA'的年龄最小的教师信息

SELECT *
FROM (SELECT * FROM teachers WHERE country='USA') T
ORDER BY T.age ASC
LIMIT 1;

执行输出结果

mysql> SELECT *
    -> FROM (SELECT * FROM teachers WHERE country='USA') T
    -> ORDER BY T.age ASC
    -> LIMIT 1;
+----+---------------+-----------------------+-----+---------+
| id | name          | email                 | age | country |
+----+---------------+-----------------------+-----+---------+
|  3 | Western Venom | [e-mail] |  28 | USA     |
+----+---------------+-----------------------+-----+---------+
1 row in set (0.00 sec)

练习题

使用内联视图查询课程表courses，查询教师id为4的学生上课人数最大的课程信息

SELECT *
FROM (SELECT * FROM courses WHERE teacher_id = 4) T
ORDER BY T.student_count DESC
LIMIT 1;

目标输出结果

mysql> SELECT *
    -> FROM (SELECT * FROM courses WHERE teacher_id = 4) T
    -> ORDER BY T.student_count DESC
    -> LIMIT 1;
+----+---------------------+---------------+------------+------------+
| id | name                | student_count | created_at | teacher_id |
+----+---------------------+---------------+------------+------------+
|  1 | Advanced Algorithms |           880 | 2020-06-01 |          4 |
+----+---------------------+---------------+------------+------------+
1 row in set (0.00 sec)