#1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
#分析:由于需要查询学生的全部信息,所以需要从分数表中查询到符合条件的分数,然后通过left join/right join进行组合
#右连接
select * from student RIGHT JOIN (
select t1.sid, courseA, courseB
from
(SELECT sid, score as courseA FROM score WHERE score.cid = '01') as t1,
(SELECT sid, score as courseA FROM score WHERE score.cid = '02') as t2
where t1.sid = t2.sid and t1.coureA > t2.courseB
)r
on student.sid = r.sid;
#左连接
select * from (
select t1.sid, courseA, courseB
from
(SELECT sid, score as courseA FROM score WHERE score.cid = '01') as t1,
(SELECT sid, score as courseA FROM score WHERE score.cid = '02') as t2
where t1.sid = t2.sid and t1.coureA > t2.courseB
) r
LEFT JOIN student
ON student.sid = r.sid;
#查询同时存在“01”课程和“02”课程的情况
select * from
(select * from score where score.cid = '01') as t1,
(select * from score where score.cid = '02') as t2
where t1.sid = t2.sid;
#查询存在"01"课程但可能不存在"02"课程的情况(不存在显示null)
#这一道就是明显需要使用join的情况了,02可能不存在,即为left join的右侧或right join 的左侧即可
#左连接
select * from
(select * from score where score.cid = '01') as t1
left join
(select * from score where score.cid = '02') as t2
on t1.sid = t2.sid;
#右连接
select * from
(select * from score where score.cid = '02') as t2
right join
(select * from score where score.cid = '01') as t1
on t1.sid = t2.sid;
#查询不存在" 01 "课程但存在" 02 "课程的情况
select * from score
where score.sid not in (
select sid from score
where score.cid = '01'
)
AND score.cid= '02';
暂时先总结到这里,待续......