题解 | #考试分数(三)#
考试分数(三)
http://www.nowcoder.com/practice/b83f8b0e7e934d95a56c24f047260d91
因为题目是要获得每个岗位分数前两名的用户，所以先通过子查询对每个岗位进行分数排名。
SELECT id, language_id, score, DENSE_RANK() OVER(PARTITION BY language_id ORDER BY score DESC ) AS rank_number FROM grade因为根据题目意思，分数一样算作并列，第二名会继续向下取，因此使用dense_rank（）函数进行排序。
随后将子查询生成的表t和language进行多表查询，匹配对应的岗位名称。
通过限制rank_number <= 2来找到分数前两名的用户。
SELECT t.id, t1.name, t.score FROM ( SELECT id, language_id, score, DENSE_RANK() OVER(PARTITION BY language_id ORDER BY score DESC ) AS rank_number FROM grade ) AS t, language AS t1 WHERE t.language_id = t1.id AND t.rank_number <= "2" ORDER BY t1.name, t.score DESC
投递华为等公司9个岗位 >
