如何在竞赛日志排名中按运动员分组，去重并保留最佳成绩

要实现每个运动员仅用最佳成绩参与排名，核心是先筛选出每个运动员的最优记录（即result最小的那条，因为升序排名中数值越小成绩越好），再基于这些记录计算排名。以下是两种适配不同MySQL版本的可行方案：

方式1：MySQL 8.0+ 用窗口函数（推荐） #

窗口函数能简洁实现分组取最优+排名的逻辑：

WITH athlete_best AS (
    SELECT 
        *,
        -- 按运动员分组，给每组内的记录按成绩升序编号，序号1就是该运动员的最佳成绩
        ROW_NUMBER() OVER (PARTITION BY ID_athlete ORDER BY result ASC) AS rn
    FROM competitionlog 
    WHERE 
        (ID_event = 19 OR ID_event = 4) 
        AND result NOT IN (0.0125, 0.00125, 0.000125)
)
SELECT 
    ID_competitionLog,
    ID_event,
    ID_athlete,
    result,
    wind,
    -- 对所有最佳成绩升序排名，支持并列（相同成绩同排名）
    RANK() OVER (ORDER BY result ASC) AS rank
FROM athlete_best
WHERE rn = 1; -- 仅保留每个运动员的最佳成绩记录

说明：如果需要连续排名（即使有并列也不跳过序号），可以把RANK()替换成DENSE_RANK()。

方式2：兼容低版本MySQL（无窗口函数） #

如果你的MySQL版本低于8.0，用子查询+关联表的方式实现：

-- 先获取每个运动员的最佳成绩（最小result）
WITH athlete_min_result AS (
    SELECT 
        ID_athlete,
        MIN(result) AS best_result
    FROM competitionlog 
    WHERE 
        (ID_event = 19 OR ID_event = 4) 
        AND result NOT IN (0.0125, 0.00125, 0.000125)
    GROUP BY ID_athlete
)
-- 关联原表获取最佳成绩对应的完整记录，再计算排名
SELECT 
    cl.ID_competitionLog,
    cl.ID_event,
    cl.ID_athlete,
    cl.result,
    cl.wind,
    FIND_IN_SET(cl.result, (
        SELECT GROUP_CONCAT(best_result ORDER BY best_result ASC)
        FROM athlete_min_result
    )) AS rank
FROM competitionlog cl
JOIN athlete_min_result amr 
    ON cl.ID_athlete = amr.ID_athlete 
    AND cl.result = amr.best_result
WHERE 
    (cl.ID_event = 19 OR cl.ID_event = 4) 
    AND cl.result NOT IN (0.0125, 0.00125, 0.000125);

原SQL问题说明 #

你当前的SQL没有对运动员进行分组去重，会把同一个运动员的所有符合条件的成绩都纳入排名计算，导致重复的运动员ID出现在结果中。上面的方案先筛选出每个运动员的唯一最佳成绩，再基于这些数据排名，刚好解决这个问题。

内容的提问来源于stack exchange，提问作者Tomas Miron