# 第一种解法
select tmp1.sno, tmp1.sname, tmp1.birth, tmp1.gender,tmp1.cno, tmp1.cname, tmp1.score,tmp2.cno, tmp2.cname, tmp2.score
from (select s.sno, s.sname, s.birth, s.gender, c.cno, c.cname, sc.score
from sc
left join course c on c.cno = sc.cno
left join student s on sc.sno = s.sno) tmp1
left join (select s.sno, s.sname, s.birth, s.gender, c.cno, c.cname, sc.score
from sc
left join course c on c.cno = sc.cno
left join student s on sc.sno = s.sno) tmp2
on tmp1.sno = tmp2.sno where tmp1.cno = 1 and tmp2.cno = 2 and tmp2.score < tmp1.score;
# 第二种解法
SELECT stu.*, s1.cno, s1.score, s2.cno, s2.score
FROM student stu
JOIN sc s1 ON stu.sno = s1.sno
JOIN sc s2 ON stu.sno = s2.sno
WHERE s1.cno = 1 AND s2.cno = 2 AND s1.score > s2.score;
总结：
- 两种解题思路一样
- 遇到需要 group by 且 需要比较group by 里面的项的时候，就需要我们 join 自己这张表，说白了就是复制本张表。
