SQL面试50题-22

链接：https://zhuanlan.zhihu.com/p/43289968

查询所有课程的成绩第2名到第3名的学生信息及该课程成绩（重要 25类似）

1.又要分组又要排序的情况 使用RANK或ROW_NUMBER或DENSE_RANK
SELECT *
,ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score ) ranking
FROM Score
2.取ranking为2和3的学生信息
WHERE ranking in (2,3)

SELECT s.s_id,s.s_name,s.s_sex,s.s_birth,n.c_id,n.s_score,n.ranking
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY c_id ORDER BY s_score ) AS ranking FROM Score) n INNER JOIN Student s ON n.s_id =s.s_id 
WHERE ranking IN (2,3)
ORDER BY n.c_id,n.ranking;