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 | [email protected] | 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;
