PostgreSQL递归查询：获取各ID的最新变更ID

我有一张用于存储各ID变更记录的changes表，希望通过递归查询获取每个ID对应的最新变更ID。

原始changes表 #

预期结果 #

原SQL的问题 #

你提供的SQL存在两处关键问题：

1. CTE定义顺序错误：递归CTE r 先定义却引用了后定义的 changes，这会触发语法错误
2. 递归无终止逻辑：当前递归会不断生成冗余关联数据，无法直接得到每个ID的最终变更结果

修正后的递归查询方案 #

方案一：分组取最大值获取最新ID #

WITH RECURSIVE id_changes AS (
    -- 锚点：初始变更记录，记录原始ID和当前变更ID
    SELECT old_id AS original_id, new_id
    FROM changes
    UNION ALL
    -- 递归：追踪每个ID的后续变更，继承原始ID
    SELECT ic.original_id, c.new_id
    FROM id_changes ic
    JOIN changes c ON ic.new_id = c.old_id
)
-- 对每个原始ID取最大的new_id，即最新变更ID
SELECT original_id, MAX(new_id) AS latest_id
FROM id_changes
GROUP BY original_id
ORDER BY original_id;

方案二：追踪最终变更节点（更直观） #

WITH RECURSIVE id_chain AS (
    -- 锚点：初始变更，记录ID链的起点、当前节点和层级
    SELECT old_id AS original_id, new_id, 1 AS chain_depth
    FROM changes
    UNION ALL
    -- 递归：沿着变更链继续追踪，层级递增
    SELECT ic.original_id, c.new_id, ic.chain_depth + 1
    FROM id_chain ic
    JOIN changes c ON ic.new_id = c.old_id
)
-- 筛选每个原始ID对应的最深层级节点，即最新变更ID
SELECT original_id, new_id AS latest_id
FROM id_chain ic
WHERE chain_depth = (
    SELECT MAX(chain_depth) 
    FROM id_chain 
    WHERE original_id = ic.original_id
)
ORDER BY original_id;

补充：包含所有中间ID的查询 #

如果需要把所有出现过的ID（比如示例中的2、3、4、1000）都作为original_id返回，可使用以下逻辑：

WITH RECURSIVE all_ids AS (
    -- 收集所有出现过的ID
    SELECT old_id AS id FROM changes
    UNION
    SELECT new_id AS id FROM changes
),
id_changes AS (
    -- 对每个ID开始追踪其最终变更
    SELECT a.id AS original_id, c.new_id
    FROM all_ids a
    LEFT JOIN changes c ON a.id = c.old_id
    UNION ALL
    SELECT ic.original_id, c.new_id
    FROM id_changes ic
    JOIN changes c ON ic.new_id = c.old_id
)
SELECT original_id, COALESCE(MAX(new_id), original_id) AS latest_id
FROM id_changes
GROUP BY original_id
ORDER BY original_id;

此版本中，无后续变更的ID（如4、1000）的latest_id会设为自身。

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