时间戳

首页 > 解决方案 > 查询名字的第二大值，如果只有一个值返回null

mysql - 查询名字的第二大值，如果只有一个值返回null

问题描述

我有一个表，它有 2 列，名为“名称”和“分数”，展示了一个班级的学业成绩。

“姓名”：安迪、艾米、克洛伊、约翰等。

“分数”：从 0 到 100

有些学生已经提交了所有的测试，但有些学生由于疫情尚未完成，换句话说，有些学生只得到了一个结果。现在我需要弄清楚他们每个人的第二高分是多少。

如果学生只提交一次，意味着他们没有第二高的分数，所以我们将返回 null 作为分数。

我试过了

SELECT 
name,
    CASE
        WHEN COUNT(score) IS NULL THEN NULL
        ELSE MAX(score)
    END AS second_highest
FROM
    result
WHERE
    score NOT IN (SELECT 
            MAX(score)
        FROM
            result
        GROUP BY name)
GROUP BY name
ORDER BY name;

但似乎子查询排除了一次提交学生的唯一分数，因此他们不会出现在查询结果中。我需要在这种方法中更改什么，还是有更好的方法？

输入和输出可以在这里找到对不起我不熟悉在 Stackoverflow 上演示示例我使用 MySQL 版本 8.0.22

标签： mysqlsql

解决方案

使用ROW_NUMBER()窗口函数对每个学生的分数进行排名，然后通过条件聚合获得第二高的分数：

SELECT name,
       MAX(CASE WHEN rn = 2 THEN score END) second_highest
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY score DESC) rn
  FROM result
) t
GROUP BY name

请参阅演示。

推荐阅读