找出每个岗位分数排名前2的用户
考试分数(三)
http://www.nowcoder.com/questionTerminal/b83f8b0e7e934d95a56c24f047260d91
两种方法：
- 使用窗口函数计算排名，再筛选排名前2的用户
- 使用自联结和count函数计算排名，再筛选排名前2的用户
使用窗口函数dense_rank()，将language_id作为排序的“窗口”，按照得分降序排列
SELECT a.id,name,score
FROM (
SELECT id,language_id,score,
DENSE_RANK() OVER (PARTITION BY language_id ORDER BY score DESC) AS r
FROM grade) AS a
INNER JOIN language
ON a.language_id=language.id
WHERE r<=2
ORDER BY name,score DESC,a.id使用自联结和count函数，表中有同一种语言得分并列的情况，所以在使用count函数时要记得使用distinct
在使用group by 进行分组时，不要遗漏用户id
SELECT a.id,name,score
FROM (
SELECT g1.language_id,g1.id,g1.score
FROM grade AS g1, grade AS g2
WHERE g1.score<=g2.score
AND g1.language_id=g2.language_id
GROUP BY g1.language_id,g1.id
HAVING COUNT(DISTINCT g2.score)<=2)AS a
INNER JOIN language
ON a.language_id=language.id
ORDER BY name,score DESC,a.id
