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

这道题目要求我们从成绩表中找出每个语言岗位分数排名前两名的用户，我们要做的事情如下：

1. 确定总体问题

我们需要找出每个语言岗位分数排名前两名的用户，并按语言名称升序、分数降序、用户ID升序排序。即要从成绩表和语言表中提取数据，使用窗口函数DENSE_RANK来计算每个岗位的分数排名

2. 分析关键问题

• 连接表：将grade表和language表连接起来，以便获取每个用户的语言岗位信息。
• 计算分数排名：使用DENSE_RANK窗口函数计算每个岗位的分数排名。
• 筛选前两名：通过WHERE子句筛选出排名前两名的记录。
• 排序输出：按语言名称升序、分数降序、用户ID升序排列输出结果。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们使用JOIN将grade表和language表连接起来：

from
    grade g
    join language l on g.language_id = l.id

• JOIN language l ON g.language_id = l.id：通过语言ID连接成绩表和语言表。

步骤2：计算分数排名

我们使用DENSE_RANK窗口函数计算每个岗位的分数排名：

dense_rank() over(partition by name order by score desc) as rk

• DENSE_RANK() OVER (PARTITION BY name ORDER BY score DESC) AS rk：计算每个岗位的分数排名，按分数降序排列。

步骤3：筛选前两名

我们通过WHERE子句筛选出排名前两名的记录：

where rk <= 2

• WHERE rk <= 2：筛选出排名前两名的记录。

步骤4：排序输出

我们按语言名称升序、分数降序、用户ID升序排列输出结果：

order by name asc, score desc, id asc

• ORDER BY name ASC, score DESC, id ASC：按语言名称升序、分数降序、用户ID升序排列。

完整代码

select
    id,
    name,
    score
from
    (
        select
            g.id,
            l.name,
            score,
            dense_rank() over(partition by name order by score desc) as rk
        from
            grade g
            join language l on g.language_id = l.id
    ) s
where rk <= 2
order by name asc, score desc, id asc;