SQL 74) 找出每个岗位分数排名前2的用户

方法）

• 先按照language的name升序排序
• 再按照积分降序排序
• 最后按照grade的id升序排序

SELECT a.id, l.name, a.score
FROM
language AS l
JOIN
(SELECT id, language_id, score, dense_rank()over(PARTITION BY language_id ORDER BY score DESC) AS rank_num
FROM grade)a
ON l.id = a.language_id
WHERE rank_num <=2
ORDER BY l.name, a.score DESC, a.id;

绝了这排序条件可真有够多的