MySQL三表查询（学生表、课程表、成绩表）查询出语文成绩比数学成绩高的学生信息

有三张表

学生表

课程表

成绩表

要求查处语文成绩低于数学成绩的学生信息

先去课程表中查出课程cid

select cid from course where cname='语文';
select cid from course where cname='数学';

然后课程cid作为查询条件去成绩表中查处所有语文成绩 作为一个结果集tempChinese 数学成绩作为一个结果集tempMath

select * from score where corse_id=(
 select cid from course where cname='语文');

select * from score where corse_id=(
 select cid from course where cname='数学');

语文成绩的结果集tempChinese 

tempMath

此时再用student tempChinese tempMath 做关联查询

select student.sid,student.sname
from student ,
(select * from score where corse_id=(
 select cid from course where cname='语文'))as tempChinese,
(select * from score where corse_id=(
 select cid from course where cname='数学'))as tempMath
where student.sid=tempChinese.sid
and tempChinese.sid = tempMath.sid
and tempChinese.number<tempMath.number;

结果