子查询
按照结果集的行列数不同，子查询可以分为以下几类：
- 标量子查询：结果集只有一行一列（单行子查询）
- 列子查询：结果集有一列多行
- 行子查询：结果集有一行多列
- 表子查询：结果集多行多列
-- 查询比小虎年龄大的所有学生
-- 标量子查询
SELECT
*
FROM
student
WHERE
age > ( SELECT age FROM student WHERE NAME = '小虎' );
-- 查询有一门学科分数大于90分的学生信息
-- 列子查询
SELECT
*
FROM
student
WHERE
id IN (
SELECT
s_id
FROM
scores
WHERE
score > 90);
-- 查询男生且年龄最大的学生
-- 行子查询
SELECT
*
FROM
student
WHERE
age = (
SELECT
max( age )
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
-- 优化
SELECT
*
FROM
student
WHERE
( age, gender ) = (
SELECT
max( age ),
gender
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
-- 查询男生且年龄最大的学生
-- 行子查询
SELECT
*
FROM
student
WHERE
age = (
SELECT
max( age )
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
-- 优化
SELECT
*
FROM
student
WHERE
( age, gender ) = (
SELECT
max( age ),
gender
FROM
student
GROUP BY
gender
HAVING
gender = '男'
)
总结：
- where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。
- where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。
-- 取排名数学成绩前五的学生，正序排列
SELECT
FROM
(
SELECT
s.*,
sc.score score,
c.NAME 科目
FROM
student s
LEFT JOIN scores sc ON s.id = sc.s_id
LEFT JOIN course c ON c.id = sc.c_id
WHERE
c.NAME = '数学'
ORDER BY
score DESC
LIMIT 5
) t
WHERE
t.gender = '男';