题解 | #考试分数(三)#

问题描述：请你找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序，得到结果如下:

方案1：窗口函数+联结language表，where过滤，order排序

SELECT t.id,l.name,t.score
FROM (SELECT *,DENSE_RANK() OVER (PARTITION BY language_id 
                                       ORDER BY score DESC) AS score_rank
      FROM grade) t
INNER JOIN language l
ON t.language_id = l.id
WHERE t.score_rank IN (1,2)
ORDER BY l.name ASC,t.score DESC,t.id ASC;

方案2：grade笛卡尔自联结，联结language,按language_id分组，having过滤

SELECT g1.id,l.name,g1.score
FROM grade g1 JOIN grade g2
ON g1.score <= g2.score AND g1.language_id = g2.language_id
INNER JOIN language l
ON g1.language_id = l.id
GROUP BY g1.id
HAVING COUNT(DISTINCT g2.score) <= 2
ORDER BY l.name ASC,g1.score DESC,g1.id ASC;

方案3：WHERE关联子查询

SELECT g1.id,l.name,g1.score
FROM grade g1
JOIN language l
ON g1.language_id = l.id
WHERE (SELECT COUNT(DISTINCT g2.score)
        FROM grade g2
        WHERE g2.score >= g1.score AND g1.language_id = g2.language_id) <=2
ORDER BY l.name ASC,g1.score DESC,g1.id ASC;

参考：https://blog.nowcoder.net/n/297da9871a8f4fd39ad939cbfc907093?f=comment