查询所有课程成绩大于80的学生姓名

分享一道今天的面试题：
查询所有课程成绩大于80的学生姓名
三个表如下

学生表 ：student

+------+--------+
| s_id | s_name |
+------+--------+
| 1 | 张三 |
| 2 | 李四 |
+------+--------+

课程表 :cource

+------+--------+
| c_id | c_name |
+------+--------+
| 1 | 语文 |
| 2 | 数学 |
+------+--------+

成绩表：source

+------+------+--------+
| s_id | c_id | result |
+------+------+--------+
| 1 | 1 | 60 |
| 1 | 2 | 90 |
| 2 | 1 | 86 |
| 2 | 2 | 84 |
+------+------+--------+

解题思路：
1.首先查询所有成绩小于80的成绩记录

select * from source where result>80;

2.然后通过完整的成绩表关联成绩小于80的成绩表，选择s_id就可以

select s1.s_id as sid
from source as s1 
left join (select * from source where result>80) as s2 on s1.s_id=s2.s_id and s1.c_id=s2.c_id
where s2.c_id is null;

执行完以上两步就可以得到有成绩小于八十的同学的id，如下
+------+
| s_id |
+------+
| 1 |
+------+

最后我们通过学生表和上面的表去选择所有成绩都大于80的同学的id，思路就是在学生表中排除上面表中的记录

最后完整的sql语句如下：

select st.s_id,st.s_name
from student as st,
(select s1.s_id as sid
from source as s1 
left join (select * from source where result>80) as s2 on s1.s_id=s2.s_id and s1.c_id=s2.c_id
where s2.c_id is null) as stu
where stu.sid <> st.s_id;