Hey，这个需求我之前帮朋友做过类似的F1积分统计系统，刚好清楚这里的坑点——总积分计算是小事，同分情况下的排名逻辑才是核心，毕竟F1有明确的官方规则，咱们一步步来搞定：

首先得锚定你要遵循的规则，F1当前的官方排名优先级是：

• 总积分更高的车手排前面
• 总积分相同的，分站冠军数量更多的车手优先
• 如果冠军数也打平，就比亚军数量，以此类推（直到第10名，因为只有前10能拿积分）
• 极端情况如果所有名次的获得次数都完全一致，就看最近一场赛事的完赛名次（名次越靠前越好）
• 真·极端情况（连最近成绩都一样）才会比最快圈速，但这种情况几乎不会出现

假设你存储单场赛事结果的表叫race_results，简化后的结构大概是这样：

CREATE TABLE race_results (
    race_id INT,          -- 分站赛事的唯一ID
    driver_id INT,        -- 车手的唯一ID
    driver_name VARCHAR(50), -- 车手姓名（建议关联单独的drivers表，这里为了简化直接写）
    finish_position INT,  -- 完赛名次（NULL代表退赛，无积分）
    points INT            -- 本场获得的积分（比如冠军25分、亚军18分，可提前计算好）
);

核心思路是：先统计每个车手的总积分和各名次的获得次数，再按官方规则排序生成排名。

第一步：统计车手的核心数据指标 #

先写一个CTE（公共表表达式），算出每个车手的总积分，以及拿到1-10名的次数：

WITH driver_stats AS (
    SELECT
        driver_id,
        driver_name,
        SUM(points) AS total_points,
        -- 统计各名次的获得次数
        COUNT(CASE WHEN finish_position = 1 THEN 1 END) AS win_count,
        COUNT(CASE WHEN finish_position = 2 THEN 1 END) AS second_count,
        COUNT(CASE WHEN finish_position = 3 THEN 1 END) AS third_count,
        COUNT(CASE WHEN finish_position = 4 THEN 1 END) AS fourth_count,
        COUNT(CASE WHEN finish_position = 5 THEN 1 END) AS fifth_count,
        COUNT(CASE WHEN finish_position = 6 THEN 1 END) AS sixth_count,
        COUNT(CASE WHEN finish_position = 7 THEN 1 END) AS seventh_count,
        COUNT(CASE WHEN finish_position = 8 THEN 1 END) AS eighth_count,
        COUNT(CASE WHEN finish_position = 9 THEN 1 END) AS ninth_count,
        COUNT(CASE WHEN finish_position = 10 THEN 1 END) AS tenth_count
    FROM race_results
    WHERE finish_position IS NOT NULL -- 只统计完赛的车手（退赛无积分也不影响名次统计）
    GROUP BY driver_id, driver_name
)

第二步：生成最终的车手积分榜（带排名） #

基于上面的统计结果，按F1规则排序，用ROW_NUMBER()生成排名：

SELECT
    ROW_NUMBER() OVER (ORDER BY 
        total_points DESC,
        win_count DESC,
        second_count DESC,
        third_count DESC,
        fourth_count DESC,
        fifth_count DESC,
        sixth_count DESC,
        seventh_count DESC,
        eighth_count DESC,
        ninth_count DESC,
        tenth_count DESC
    ) AS driver_rank,
    driver_id,
    driver_name,
    total_points,
    win_count,
    second_count
FROM driver_stats
ORDER BY driver_rank;

补充：处理极端同分情况（所有名次次数都一致） #

如果真的遇到两个车手总积分相同，且1-10名的获得次数完全一样，就需要对比他们最近一场赛事的成绩。这时候需要额外统计每个车手的最近完赛名次：

先新增一个CTE获取每个车手的最近赛事成绩：

WITH driver_latest_race AS (
    SELECT
        driver_id,
        finish_position AS latest_finish_pos
    FROM race_results
    WHERE finish_position IS NOT NULL
    -- 取每个车手最近的一场完赛记录
    QUALIFY ROW_NUMBER() OVER (PARTITION BY driver_id ORDER BY race_id DESC) = 1
),
driver_stats AS (
    SELECT
        dr.driver_id,
        dr.driver_name,
        SUM(dr.points) AS total_points,
        -- 各名次统计和之前一样
        COUNT(CASE WHEN dr.finish_position = 1 THEN 1 END) AS win_count,
        COUNT(CASE WHEN dr.finish_position = 2 THEN 1 END) AS second_count,
        COUNT(CASE WHEN dr.finish_position = 3 THEN 1 END) AS third_count,
        COUNT(CASE WHEN dr.finish_position = 4 THEN 1 END) AS fourth_count,
        COUNT(CASE WHEN dr.finish_position = 5 THEN 1 END) AS fifth_count,
        COUNT(CASE WHEN dr.finish_position = 6 THEN 1 END) AS sixth_count,
        COUNT(CASE WHEN dr.finish_position = 7 THEN 1 END) AS seventh_count,
        COUNT(CASE WHEN dr.finish_position = 8 THEN 1 END) AS eighth_count,
        COUNT(CASE WHEN dr.finish_position = 9 THEN 1 END) AS ninth_count,
        COUNT(CASE WHEN dr.finish_position = 10 THEN 1 END) AS tenth_count,
        dl.latest_finish_pos
    FROM race_results dr
    LEFT JOIN driver_latest_race dl ON dr.driver_id = dl.driver_id
    WHERE dr.finish_position IS NOT NULL
    GROUP BY dr.driver_id, dr.driver_name, dl.latest_finish_pos
)

然后在排序规则里加入latest_finish_pos ASC（因为名次越小越好，比如1比2优先级高）：

SELECT
    ROW_NUMBER() OVER (ORDER BY 
        total_points DESC,
        win_count DESC,
        second_count DESC,
        third_count DESC,
        fourth_count DESC,
        fifth_count DESC,
        sixth_count DESC,
        seventh_count DESC,
        eighth_count DESC,
        ninth_count DESC,
        tenth_count DESC,
        latest_finish_pos ASC
    ) AS driver_rank,
    driver_id,
    driver_name,
    total_points,
    win_count,
    second_count,
    latest_finish_pos
FROM driver_stats
ORDER BY driver_rank;

• 建议把车手信息存在单独的drivers表，通过driver_id关联查询，避免race_results里的driver_name出现重名或数据不一致的问题
• 如果F1的积分规则有变动（比如新增冲刺赛积分），可以把积分规则存在points_system表，通过finish_position关联获取积分，这样后续修改规则更灵活
• 给race_results表的driver_id、finish_position、race_id字段加索引，能大幅提升统计查询的速度，尤其是数据量较大的时候

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