PostgreSQL交替排名查询：运动员跳高跳远交替排序实现

表结构与初始数据 #

CREATE TABLE athletes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    high_jump_score DECIMAL,
    long_jump_score DECIMAL
);

-- 初始测试数据
INSERT INTO athletes (name, high_jump_score, long_jump_score) VALUES
    ('Canga Roo', 2, 8),
    ('Bob Johnson', 1.90, 7.5),
    ('John Doe', 1.85, 7.2),
    ('Jane Smith', 1.75, 6.8),
    ('Alice Brown', 1.76, 6.7);

需求说明 #

需要编写SQL实现交替选取剩余运动员中最高成绩者的逻辑：

1. 每次循环先从剩余运动员中选取当前跳高成绩最高的，加入结果并标记为已选中
2. 再从剩余运动员中选取当前跳远成绩最高的（因上一步已移除选中者，无需额外处理同一人重复问题），加入结果并标记为已选中
3. 重复上述步骤，直到所有运动员都被处理
4. 每位运动员仅出现一次，结果需展示其被选中时的项目排名（如1st Hi-Jump）

用户尝试的问题 #

使用ROW_NUMBER()分别获取跳高、跳远排名后，按最小排名分组排序的方案存在逻辑缺陷，会出现运动员排名错位（如Alice Brown被错误排在首位）的问题。

补充测试数据与预期结果 #

补充数据 #

INSERT INTO athletes (name, high_jump_score, long_jump_score) VALUES
    ('Canga Roo', 10, 10),
    ('Bob Johnson', 1, 5),
    ('John Doe', 2, 4),
    ('Jane Smith', 3, 3),
    ('Alice Brown', 4, 2);

预期结果 #

Canga Roo, 1st Hi Jump
Bob Johnson, 1st Long Jump
Alice Brown, 2nd Hi Jump
John Doe, 2nd Long Jump
Jane Smith, 3rd Hi Jump

解决方案：递归CTE实现循环选取 #

递归CTE适合这种逐步处理剩余数据集的场景，核心是每次迭代仅选取符合条件的单个运动员，同时维护已选中的集合，直到所有运动员被处理。

WITH RECURSIVE selection_process AS (
    -- 基础步骤：选取第一个跳高最高的运动员
    SELECT
        a.id,
        a.name,
        'Hi-Jump' AS event_type,
        1 AS rank_in_event,
        ARRAY[a.id] AS selected_ids,
        1 AS step_order
    FROM athletes a
    WHERE high_jump_score = (SELECT MAX(high_jump_score) FROM athletes)
    LIMIT 1 -- 若有同分，取第一个（可根据需求调整排序规则）

    UNION ALL

    -- 递归步骤：交替选取跳远/跳高最高的剩余运动员
    SELECT
        next_athlete.id,
        next_athlete.name,
        CASE sp.event_type
            WHEN 'Hi-Jump' THEN 'Long-Jump'
            ELSE 'Hi-Jump'
        END AS event_type,
        -- 计算当前项目在剩余运动员中的排名
        ROW_NUMBER() OVER (
            ORDER BY 
                CASE WHEN sp.event_type = 'Hi-Jump' THEN next_athlete.long_jump_score ELSE next_athlete.high_jump_score END DESC
        ) AS rank_in_event,
        sp.selected_ids || next_athlete.id AS selected_ids,
        sp.step_order + 1 AS step_order
    FROM selection_process sp
    -- 筛选未被选中的运动员
    CROSS JOIN LATERAL (
        SELECT a.id, a.name, a.high_jump_score, a.long_jump_score
        FROM athletes a
        WHERE a.id <> ALL(sp.selected_ids)
        ORDER BY 
            CASE WHEN sp.event_type = 'Hi-Jump' THEN a.long_jump_score ELSE a.high_jump_score END DESC,
            a.id -- 同分情况下按id排序，保证结果稳定
        LIMIT 1
    ) next_athlete
    -- 终止条件：还有未选中的运动员
    WHERE array_length(sp.selected_ids, 1) < (SELECT COUNT(*) FROM athletes)
),
-- 转换排名数字为序数词
rank_ordinal AS (
    SELECT
        name,
        event_type,
        step_order,
        CASE rank_in_event
            WHEN 1 THEN '1st'
            WHEN 2 THEN '2nd'
            WHEN 3 THEN '3rd'
            ELSE CONCAT(rank_in_event, 'th')
        END AS ordinal_rank
    FROM selection_process
)
-- 生成最终结果
SELECT CONCAT(name, '; ', ordinal_rank, ' ', event_type) AS result
FROM rank_ordinal
ORDER BY step_order;

代码说明 #

1. 递归CTE核心逻辑：

   • 基础步骤初始化选取第一个跳高最高的运动员，记录已选中ID集合和步骤顺序
   • 递归步骤根据上一步的项目类型，切换选取剩余运动员中对应项目的最高成绩者，更新已选中集合和步骤顺序
   • 终止条件为已选中的运动员数量等于总数量

2. 序数词转换：通过CASE语句将数字排名转换为符合要求的序数格式（1st、2nd等）

3. 稳定性处理：当出现成绩同分的情况，通过a.id排序保证结果的确定性（可根据需求调整为其他排序规则，如姓名）

测试验证 #

将上述SQL分别运行在两组测试数据上，均可得到与预期一致的结果。

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