【mysql经典题】1、查询"1"课程比"2"课程成绩高的学生的

# 第一种解法
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 自己这张表，说白了就是复制本张表。