如何基于ID变更记录表计算并添加最新ID列？

这场景属于典型的递归层级追溯，ID可能经过多轮变更（比如1→10→20），得顺着链条找到末端没有后续new_id的最终ID。你之前用的LEAD只能取相邻行，没法处理多层级的情况，试试用递归CTE（公共表表达式）来解决，它能自动遍历所有变更链条，不用提前知道变更次数。

具体SQL实现 #

假设你的表名为id_changes，替换成你实际的表名即可：

WITH RECURSIVE id_tracking AS (
    -- 锚点成员：先把所有记录作为起始点，标记原始ID和当前追踪的ID
    SELECT
        id AS original_id,
        id AS current_id,
        new_id,
        modification_date
    FROM id_changes

    UNION ALL

    -- 递归成员：沿着new_id继续追踪下一个ID节点，直到new_id为空
    SELECT
        it.original_id,
        ic.id AS current_id,
        ic.new_id,
        ic.modification_date
    FROM id_tracking it
    JOIN id_changes ic ON it.new_id = ic.id
    WHERE it.new_id IS NOT NULL
)
-- 提取每个原始ID对应的最终最新ID
SELECT
    t.id,
    t.new_id,
    t.modification_date,
    -- 取链条末端（new_id为空）的current_id作为latest_id
    MAX(it.current_id) FILTER (WHERE it.new_id IS NULL) AS latest_id
FROM id_changes t
JOIN id_tracking it ON t.id = it.original_id
GROUP BY t.id, t.new_id, t.modification_date
-- 只保留初始节点（比如1、3，过滤掉中间节点10、20）
HAVING t.new_id IS NULL OR EXISTS (
    SELECT 1 FROM id_changes WHERE id = t.new_id
);

逻辑说明 #

1. 递归CTE的id_tracking会生成每个原始ID的完整变更链条：比如ID1的链条是1→10→20，ID3的链条只有3。
2. 最后通过分组筛选，取每个原始ID对应的链条末端（new_id IS NULL）的current_id，就是我们要的latest_id。
3. 最后的HAVING子句用来过滤掉中间变更节点（比如10、20），只保留你预期结果里的初始节点（1、3）。

这个方法支持任意次数的ID变更，只要数据库支持WITH RECURSIVE（主流数据库如PostgreSQL、MySQL 8.0+、SQL Server、Oracle 11g+都支持）。

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