SQL Server 基于CTE实现全量终端ID映射历史关联ID的方法

针对SQL Server中ID交叉映射表的全链路追溯需求，不需要复杂游标或多层嵌套关联，通过递归CTE即可高效实现目标，核心逻辑和可落地脚本如下。

核心实现逻辑 #

• 首先定位终端有效ID：交叉表中从未作为其他映射行的PreviousId出现的CurrentId，就是不存在后续变更的最终有效ID（终端ID）
• 反向递归溯源：从终端ID出发，沿着映射表的CurrentId = PreviousId关联关系逐级向上回溯，找到每一层对应的历史旧ID
• 增加递归防护：给递归层级设置上限，避免脏数据导致的循环递归死循环
• 结构化输出结果：按终端ID分组整理所有关联的历史ID，同时输出完整迁移路径方便校验

可直接复用的执行脚本 #

-- 临时表#ExampleIdCrosswalk基础结构（已建表可跳过）
CREATE TABLE #ExampleIdCrosswalk (
    CurrentId VARCHAR(50),
    PreviousId VARCHAR(50),
    PreviousIdObsoleteDate DATETIME
)

-- 测试数据示例：模拟多跳ID迁移链路
INSERT INTO #ExampleIdCrosswalk (CurrentId, PreviousId, PreviousIdObsoleteDate)
VALUES
('ID_B', 'ID_A', '2022-03-15'),
('ID_C', 'ID_B', '2023-02-20'),
('ID_F', 'ID_E', '2023-07-01')

-- 递归CTE核心追溯逻辑
WITH IdFullTrail AS (
    -- 锚点：锁定所有终端最新ID
    SELECT
        CurrentId AS TerminalId,
        CurrentId AS LinkedId,
        1 AS TrailStep,
        CAST(CurrentId AS VARCHAR(2000)) AS FullMigrationPath
    FROM #ExampleIdCrosswalk
    -- 终端ID判断：没有被后续映射当成旧ID
    WHERE CurrentId NOT IN (
        SELECT DISTINCT PreviousId 
        FROM #ExampleIdCrosswalk 
        WHERE PreviousId IS NOT NULL
    )

    UNION ALL

    -- 递归段：反向追溯每一级历史ID
    SELECT
        t.TerminalId,
        c.PreviousId AS LinkedId,
        t.TrailStep + 1 AS TrailStep,
        CAST(t.FullMigrationPath + ' ← ' + c.PreviousId AS VARCHAR(2000)) AS FullMigrationPath
    FROM IdFullTrail t
    INNER JOIN #ExampleIdCrosswalk c
        ON t.LinkedId = c.CurrentId
    -- 递归上限防护，最多追溯100层链路，避免循环死锁
    WHERE t.TrailStep < 100
)

-- 输出最终结果
SELECT
    TerminalId,
    LinkedId AS MappedHistoryId,
    TrailStep,
    FullMigrationPath
FROM IdFullTrail
ORDER BY TerminalId, TrailStep DESC

结果校验说明 #

• 输出结果中每个终端ID会对应全链路所有关联ID，包含自身（最新状态）和所有历史变更前的旧ID
• 可通过FullMigrationPath字段快速核对迁移链路的顺序是否符合预期
• 如果需要聚合展示单个终端ID下的所有历史ID，可在最终查询外层加STRING_AGG(LinkedId, ',')按TerminalId分组聚合即可

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