【DA】-【SQL】-【B站_SQL面试50题】- 01_查询课程01比课程02成绩高的学生的学号

0. 来源&简介

• B站视频-【数据分析】- SQL面试50题 - 跟我一起打怪升级 一起成为数据科学家-https://www.bilibili.com/video/av55971363?p=4
• 知乎专栏-SQL面试必会50题-https://zhuanlan.zhihu.com/p/43289968
• 查询课程01比课程02成绩高的学生的学号

1.主要内容

因为要对比两门课程的成绩，所以考虑将 【课程01】 查询结果和 【课程02】 查询结果作inner join.

select 
a.s_id '学号', a.s_score '课程01', b.s_score '课程02' 
from
(
select s_id, c_id, s_score from score where c_id = '01'
) as a
INNER JOIN
(
select s_id, c_id, s_score from score where c_id = '02'
) as b
on a.s_id = b.s_id
where a.s_score>b.s_score;

若要获得姓名等其他信息，可以继续inner join其他表，如student表。

select s.s_name '姓名', a.s_id '学号', a.s_score '课程01', b.s_score '课程02' from
(
select s_id, c_id, s_score from score where c_id = '01'
) as a
INNER JOIN
(
select s_id, c_id, s_score from score where c_id = '02'
) as b
on a.s_id = b.s_id
INNER JOIN student s on s.s_id = b.s_id 
where a.s_score>b.s_score;

2.其他

• 内连接等：
  
• 图片参考自：
  数据库左连接、右连接、内连接、全连接笔记-https://blog.csdn.net/u014204541/article/details/79739980