mysql中查询每门课程成绩最好的前两名

       有2个表，score表跟course表。score存放所有学生的课程成绩，course存放所有课程的信息。现在要查处每个课程下成绩前2名的学生。

第一种方法：

只把课程表作为主表，分别查询第一名、第二名的学生id或姓名。

SELECT CO.cname 课程,
       (select sc.student_id
          from score sc
         where sc.course_id = co.cid
         order by sc.num limit 0, 1) 第一名,
       (select sc.student_id
          from score sc
         where sc.course_id = co.cid
         order by sc.num limit 1, 1) 第二名
  from course co;

       这种方法比较好理解，但效率比较低一点，要把score表查询2遍。于是想到oracle中用with。。。as创建临时表，想到这样的写法。

with t as
(select sc.student_id
          from score sc
         where sc.course_id = co.cid
         order by sc.num)

SELECT CO.cname 课程,
       (select * from t limit 0, 1) 第一名,
       (select * from t limit 1, 1) 第二名
  from course co;

      运行后报错，发现自己是5.5版本，不支持with as的写法，高版本的可以试下。

第二种方法：

select sc.course_id, sc.student_id
  from score sc
  left join score sb
    on sc.course_id = sb.course_id
   and sc.num < sb.num
 group by sc.course_id, sc.student_id
having count(sc.course_id) < 2

      这种方法的原理是查询同一门课程中，比自己分数高的学生的数量，第一名比自己高的数量为0，第二名为1，第三名为2.以此类推，所以只要查询count()<2即可