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

两种方法：

1. 使用窗口函数计算排名，再筛选排名前2的用户
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