子查询
where(这个值是计算出来的)
先执行where里面的语句,之后执行外面的语句
本质:where里面嵌套一个子查询语句
查询参加高等数学考试的学生(学号,姓名。科目编号,成绩),降序排列
-- 方式一:连接查询
SELECT s.studentno,studentname,r.subjectno,studentresult
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN `subject`sub
on sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'
order by studentresult DESC
-- 方式二:子查询
SELECT s.studentno,studentname,r.subjectno,studentresult
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE subjectno=(
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
)
order by studentresult DESC
-- 查询分数不小于80的学生的学号,姓名
SELECT s.studentno,studentname
from student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE studentresult>=80
-- 查询参加高等数学-1分数不小于80的学生的学号,姓名
SELECT s.studentno,studentname
from student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE studentresult>=80
and
subjectno=(
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
)
-- 查询参加高等数学-1分数不小于80的学生的学号,姓名
-- 嵌套查询(由里及外)
SELECT studentno,studentname
from student
WHERE studentno in(
SELECT studentno FROM result WHERE studentresult>=80
and
subjectno=(
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
))
-- 查询参加高等数学-1,前5名同学的信息(学号,姓名,成绩)
-- 联表查询
SELECT s.studentno,studentname,studentresult
from student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE subjectname='高等数学-1'
ORDER BY studentresult DESC
LIMIT 0,5
-- 查询参加高等数学-1,前5名同学的信息(学号,姓名,成绩)
-- 子查询
SELECT s.studentno,studentname,studentresult
from student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE subjectno= (
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
)
ORDER BY studentresult DESC
LIMIT 0,5
标签:studentno,查询,嵌套,studentresult,subjectno,MySQL,WHERE,SELECT
From: https://www.cnblogs.com/yzx-sir/p/17399717.html