SQL技术咨询：按ID分组将排名时间值转为多列的可扩展实现方法

我明白你想要实现的是同一id分组下，把指定排名（2、3、4）的time提取成固定列，不管当前行的rnk是多少，每个id对应的这些列值都保持一致。之前用多次Join的方式确实不够灵活——要是后续要加更多排名列（比如第五、第六名），就得不断加Join语句，维护起来太麻烦。这里给你两个更具扩展性的方案：

方案1：先聚合再关联（适合大数据量场景） #

先通过分组聚合，把每个id下对应排名的time提前计算好，再和原表关联，这样只需要一次聚合操作，后续加列只需要修改聚合部分：

-- 先计算每个id对应的指定排名时间
WITH id_rank_times AS (
    SELECT 
        id,
        -- 提取排名2的时间
        MAX(CASE WHEN rnk = 2 THEN time END) AS second,
        -- 提取排名3的时间
        MAX(CASE WHEN rnk = 3 THEN time END) AS third,
        -- 提取排名4的时间
        MAX(CASE WHEN rnk = 4 THEN time END) AS fourth
    FROM your_table
    GROUP BY id
)
-- 关联原表得到最终结果
SELECT 
    t.id,
    t.rnk,
    t.time,
    rt.second,
    rt.third,
    rt.fourth
FROM your_table t
JOIN id_rank_times rt ON t.id = rt.id
ORDER BY t.id, t.rnk;

为什么这个方案更好？ #

• 扩展性拉满：如果要加fifth（排名5的时间），只需要在CTE里加一行MAX(CASE WHEN rnk = 5 THEN time END) AS fifth就行，不用改关联逻辑。
• 性能更优：对于大数据量，先聚合出每个id的少量结果，再关联比多次Join的效率高很多。

方案2：窗口函数直接计算（代码更简洁） #

如果你的数据库支持窗口函数（比如MySQL 8+、PostgreSQL、SQL Server等），可以直接在原表上用窗口函数计算，连关联都省了：

SELECT 
    id,
    rnk,
    time,
    -- 按id分区，取当前id下排名2的时间
    MAX(CASE WHEN rnk = 2 THEN time END) OVER (PARTITION BY id) AS second,
    MAX(CASE WHEN rnk = 3 THEN time END) OVER (PARTITION BY id) AS third,
    MAX(CASE WHEN rnk = 4 THEN time END) OVER (PARTITION BY id) AS fourth
FROM your_table
ORDER BY id, rnk;

这个方案的优势： #

• 代码更简洁，不需要额外的子查询或CTE，逻辑一目了然。
• 同样具备扩展性，新增列只需要复制一行窗口函数代码，修改排名值即可。

效果验证 #

用你给出的示例数据测试，这两个方案都会输出你期望的结果：

• 对于id=123，second固定为13:21:17.184（rnk=2的time），third为13:33:00.790，fourth为13:47:27.566，所有行的这三列值都一致。
• 对于id=789，因为没有rnk=4的记录，fourth会返回null，符合你的示例。

如果后续需要支持动态的排名数量（比如不确定要提取到第几名），可以考虑用动态SQL生成对应的列，但如果是固定的几个排名，上面的方案完全足够，比多次Join的方式灵活太多。

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