mysql子查询联系

1.横向显示学生所学课程的成绩（例如：学生姓名、学生课程1的分数、学生课程2的分数）
select s_name,a.s_score,b_score from
(select s_id,s_score from Score where c_id = 1) as a left join
(select s_id,s_score from Score where c_id = 2) as b on b.s_id = a.s_id left join Student on Student.s_id = a.s_id
where a.s_score > b.s_score;
2.找出直播带货课程的成绩，比如培训英语成绩高的学生
select s_name from
(select s_id,s_score from Score where c_id = 1) as a left join
(select s_id,s_score from Score where c_id = 2) as b on b.s_id = a.s_id
left join Student on Student.s_id = a.s_id
where a.s_score < b.s_score;
3.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select Student.s_id,Student.s_name from
Score left join Student on Student.s_id = Score.s_id
group by Student.s_name having avg(Score.s_score) >= 60 ;
4.查询在教师——今日头条老板上过课的学生的个人信息
select s_id,s_name,s_birth,s_sex from Teacher left join Course on Course.t_id = Teacher.t_id left join Score on Score.c_id = Course.c_id left join Student on Student.s_id = Score.s_id where t_name = ‘今日头条老板’ group by s_id;
5.查询比学生罗永浩在课程直播卖货的成绩高的学生信息
select s_id,s_name,s_birth,s_sex from Course left join Score on Score.c_id = Course.c_id left join Student on Student.s_id = Score.s_id where c_name = ‘直播卖货’ as a group by s_id having a.s_score > (select s_score from a where s_name = ‘罗永浩’);
6.查询不同教师教授的课程平均分。（例如：教师，课程1的平均分，课程2的平均分）
select t_name,c_name,avg(s_score) from Teacher left join Course on Course.t_id = Teacher.t_id left join (select c_id,s_score from Score where c_id = 1) as a on a.c_id = Course.c_id left join (select c_id,s_score from Score where c_id = 2) as b on b.c_id = Course.c_id group by t_name;