查询“01“课程比“02“课程成绩高的学生的信息及课程分数 sql语句的详解

-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数	
	
select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
student a 
	join score b on a.s_id=b.s_id and b.c_id='01'
	left join score c on a.s_id=c.s_id and c.c_id='02' or c.c_id = NULL where b.s_score>c.s_score

select a.* ,b.s_score as 01_score  from 
student a 
	join score b on a.s_id=b.s_id and b.c_id='01'

select a.* ,b.s_score as 01_score,c.s_score as 02_score from 
student a 
	join score b on a.s_id=b.s_id and b.c_id='01'
	以上是虚拟表，下面是和成绩表合并，查出02课程的全部的信息
	left join score c on a.s_id=c.s_id and c.c_id='02'