SQL如何按参与者分组优先取最高轮次成绩并正确排名

现有存储赛事成绩的match_score表，表结构及测试数据如下：

字段规则：round值为1代表预赛，值为2代表决赛。
直接使用group by participant配合order by score desc的写法无法正确排名：这类写法没有指定分组后取哪个轮次的成绩，多数数据库会默认返回分组内最早存储的预赛成绩，最终得到vlad第1、duff第2、gabe第3的错误结果，不符合业务要求。

• 每个参赛者优先取最高轮次（round数值更大）的成绩作为排名依据：进入决赛的选手取决赛成绩，未进入决赛的选手取预赛成绩
• 确定有效排名成绩后，按成绩从高到低降序排列
• 预期排名结果：
  • 第1名：gabe，决赛成绩75分
  • 第2名：john，决赛成绩70分
  • 第3名：vlad，预赛成绩85分
  • 第4名：duff，预赛成绩80分

根据使用的数据库版本不同，可选择以下两种写法：

方案1：窗口函数写法（推荐，适配支持窗口函数的数据库：MySQL8.0+、PostgreSQL、SQL Server等） #

核心逻辑是先给每个选手的所有参赛记录按轮次倒序编号，筛选出每个选手最高轮次的记录后，再按成绩排序生成排名。

SELECT
    participant,
    round,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM (
    SELECT
        participant,
        round,
        score,
        ROW_NUMBER() OVER (PARTITION BY participant ORDER BY round DESC) AS rn
    FROM match_score
) t
WHERE rn = 1
ORDER BY score DESC;

方案2：关联聚合查询写法（兼容旧版不支持窗口函数的数据库，如MySQL5.x） #

核心逻辑是先聚合计算出每个选手的最高参赛轮次，再关联原表取出对应轮次的成绩，最后按成绩排序。

SELECT
    m.participant,
    m.round,
    m.score
FROM match_score m
INNER JOIN (
    SELECT 
        participant,
        MAX(round) AS max_round
    FROM match_score
    GROUP BY participant
) t ON m.participant = t.participant AND m.round = t.max_round
ORDER BY m.score DESC;

两种写法执行后均会返回符合预期的排名结果：

内容的提问来源于stack exchange，提问作者Rizlan Nawfal Tamima