查询“生物”课程比“物理”课程成绩高的所有学生的学号
首先来查找一下需要用的数据
第一步是先将生物和物理所有学生的ID(student_id)和成绩(num)
select student_id,num from score join course on score.course_id=course.cid where course.cname='生物'
select student_id,num from score join course on score.course_id=course.cid where course.cname='物理'
第二步将生物表(s)和物理表(w)进行连接
(select student_id,num from score join course on score.course_id=course.cid where course.cname='生物')s
join
(select student_id,num from score join course on score.course_id=course.cid where course.cname='物理')w
on s.student_id=w.student_id
join student
on s.student_id=student.sid
最后加以条件,得出查询的结果
select
s.student_id,student.sname
from
(select student_id,num from score join course on score.course_id=course.cid where course.cname='生物')s
join
(select student_id,num from score join course on score.course_id=course.cid where course.cname='物理')w
on s.student_id=w.student_id
join student
on s.student_id=student.sid
where s.num>w.num;