mysql 三表查询

三表查询

学生成绩表sc

学生表student

课程表

要求查询数学成绩小于语文成绩的学生姓名

use exercise0107;-- 数据库的名称
--  第一步查询语文，数学的课程号
select cid from course where cname="语文";
select cid from course where cname="数学";
-- 第二步分别查询每个人的语文成绩与数学成绩  并给予名称 tempChinese tempMath
select * from sc where (select cid from course where cname="语文") ;
select * from sc where (select cid from course where cname="数学") ;
-- 第三步联合学生表查出数学成绩小于语文成绩的学生姓名
select student.sname from student, 
(select * from sc where (select cid from course where cname="语文") ) as tempChinese, 
(select * from sc where (select cid from course where cname="数学") ) as tempMath
where tempChinese.sid=student.sid and 
tempChinese.sid=tempMath.sid and
tempChinese.score>tempMath.score;