本质:在where语句中嵌套一个子查询语句
题目:查询高等数学-2的所有考试结果(学号,考试编号,成绩)降序排序
-- 查询高等数学-2的所有考试结果(学号,考试编号,成绩)降序排序
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` AS r
INNER JOIN `subject` AS sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE `SubjectName`='高等数学-2'
ORDER BY `StudentResult` DESC
结果:
对上述题目用子查询改写:
-- 使用子查询,
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE `SubjectNo`=(
SELECT `SubjectNo` FROM `subject`
WHERE `SubjectName`='高等数学-2'
)
效果和上述一样
题目:分数不小于70分的学生的学号和姓名
-- 普通查询:分数不小于70分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student AS s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` AS sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectName = '高等数学-2' AND `StudentResult`>'70'
结果:
使用子查询改写:
-- 使用子查询:分数不小于70分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student AS s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
WHERE `StudentResult`>=70 AND `SubjectNo`=(
SELECT `SubjectNo`FROM `subject`
WHERE `SubjectName`='高等数学-2'
)
结果和上述一样
对整个子查询进行改写
-- 再改造
SELECT `StudentNo`,`StudentName` FROM student WHERE `studentNo` IN(
SELECT `StudentNo` FROM `result` WHERE `StudentResult`>70 AND `SubjectNo`=(
SELECT `SubjectNo` FROM `subject` WHERE `SubjectName`='高等数学-2'
)
)