PostgreSQL实现5人循环赛排名：三方平局逻辑处理需求

没问题，咱们一步步来搞定这个三方平局的排名难题！首先得明确，循环赛里的三方平局（比如A胜B、B胜C、C胜A）确实是之前简单胜负逻辑搞不定的情况，得结合内部对战成绩和**次级tiebreaker（比如净胜分、总得分）**来处理。

先假设你的versus表结构大概是这样（如果和你的实际结构有出入，直接调整字段名就行）：

CREATE TABLE versus (
    match_id SERIAL PRIMARY KEY,
    team_a_id INT,
    team_b_id INT,
    team_a_score INT,
    team_b_score INT
);

接下来，咱们用CTE（公共表表达式）分阶段计算排名，最终把结果写入你的新表T：

第一步：计算各队伍的基础统计数据 #

先算出每个队伍的总胜场、总负场、总得分、总失分、净胜分，这是排名的核心基础：

WITH team_stats AS (
    SELECT
        team_id,
        COUNT(CASE WHEN score > opponent_score THEN 1 END) AS wins,
        COUNT(CASE WHEN score < opponent_score THEN 1 END) AS losses,
        SUM(score) AS total_points,
        SUM(opponent_score) AS total_opponent_points,
        SUM(score - opponent_score) AS point_difference
    FROM (
        -- 把每场比赛拆成两条记录，分别对应两个队伍的视角
        SELECT team_a_id AS team_id, team_a_score AS score, team_b_score AS opponent_score FROM versus
        UNION ALL
        SELECT team_b_id AS team_id, team_b_score AS score, team_a_score AS opponent_score FROM versus
    ) AS all_teams
    GROUP BY team_id
),

第二步：识别平局分组，计算组内对战成绩 #

把基础统计完全相同的队伍归为一组，然后计算每组内队伍互相对战的成绩（只看组内队伍之间的比赛），用这个内部成绩打破平局：

tie_groups AS (
    SELECT
        wins,
        losses,
        point_difference,
        ARRAY_AGG(team_id ORDER BY team_id) AS tied_teams
    FROM team_stats
    GROUP BY wins, losses, point_difference
    HAVING COUNT(*) >= 2 -- 只处理存在平局的组
),
intra_group_stats AS (
    SELECT
        ts.team_id,
        tg.tied_teams,
        -- 计算平局组内的胜场数
        COUNT(CASE WHEN vs.winner_id = ts.team_id THEN 1 END) AS intra_wins,
        -- 计算组内的净胜分
        SUM(CASE
            WHEN vs.team_a_id = ts.team_id THEN vs.team_a_score - vs.team_b_score
            WHEN vs.team_b_id = ts.team_id THEN vs.team_b_score - vs.team_a_score
            ELSE 0
        END) AS intra_point_diff
    FROM team_stats ts
    JOIN tie_groups tg ON ts.team_id = ANY(tg.tied_teams)
    JOIN versus vs ON (vs.team_a_id = ts.team_id AND vs.team_b_id = ANY(tg.tied_teams))
                   OR (vs.team_b_id = ts.team_id AND vs.team_a_id = ANY(tg.tied_teams))
    -- 提前标记每场比赛的胜者，方便后续统计
    CROSS JOIN LATERAL (
        SELECT CASE WHEN vs.team_a_score > vs.team_b_score THEN vs.team_a_id ELSE vs.team_b_id END AS winner_id
    ) AS w
    GROUP BY ts.team_id, tg.tied_teams
)

第三步：综合规则生成最终排名并写入表T #

结合基础统计、组内对战成绩，以及次级tiebreaker生成最终排名，插入到你的新表T中：

INSERT INTO T (team_id, rank, wins, losses, point_difference, intra_wins, intra_point_diff)
SELECT
    ts.team_id,
    -- 用DENSE_RANK实现同排名不跳号的效果，符合电竞排名习惯
    DENSE_RANK() OVER (
        ORDER BY
            ts.wins DESC,
            ts.point_difference DESC,
            -- 基础数据相同时，优先看组内胜场
            COALESCE(igs.intra_wins, 0) DESC,
            -- 组内胜场仍相同，看组内净胜分
            COALESCE(igs.intra_point_diff, 0) DESC,
            -- 最后用总得分兜底
            ts.total_points DESC
    ) AS rank,
    ts.wins,
    ts.losses,
    ts.point_difference,
    COALESCE(igs.intra_wins, 0) AS intra_wins,
    COALESCE(igs.intra_point_diff, 0) AS intra_point_diff
FROM team_stats ts
LEFT JOIN intra_group_stats igs ON ts.team_id = igs.team_id
ORDER BY rank, ts.team_id;

关键逻辑说明 #

• 基础排名规则：优先按总胜场排序，然后是净胜分，这是循环赛的常规判定逻辑。
• 三方平局破局：当多个队伍基础统计完全相同时，只看他们之间的内部对战成绩——组内胜场多的队伍排名靠前；如果是三方循环胜负（各1胜1负），就看组内净胜分，仍相同则看总得分。
• DENSE_RANK的作用：这个函数可以让同排名的队伍获得相同名次，不会跳过数字（比如1,1,2而非1,1,3），完全匹配电竞赛事的排名展示需求。

你可以根据赛事的实际规则调整排序优先级，比如如果更看重总得分而非净胜分，直接把ts.total_points DESC提前到ts.point_difference DESC前面就行。

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