mysql:查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

方法一：

/*查询所有课程的成绩第2名到第3名的学生信息及该课程成绩*/
select cid,s.*,score
from Student s
inner join (
    select sid,score,cid, row_number() over (partition by cid order by score desc) as r
    from SC
    ) t
on t.sid=s.sid
where t.r in(2,3);

方法二：

select t1.* from
(select st.*,c.cid,c.cname,sc.score from Student st
left join SC sc on sc.sid=st.sid
inner join Course c on c.cid =sc.cid and c.cid='001'
order by sc.score desc limit 1,2) t1

union all

select t2.* from
(select st.*,c.cid,c.cname,sc.score from Student st
left join SC sc on sc.sid=st.sid
inner join Course c on c.cid =sc.cid and c.cid='002'
order by sc.score desc limit 1,2) t2

union all

select t3.* from
(select st.*,c.cid,c.cname,sc.score from Student st
left join SC sc on sc.sid=st.sid
inner join Course c on c.cid =sc.cid and c.cid='003'
order by sc.score desc limit 1,2) t3;