求助：使用递归CTE优化SQL关联查询，获取最新活跃ID

我完全懂你的痛点——原来的SQL只能处理单次合并的情况，没法应对ID状态互换、或者多次合并的链式追溯问题，递归CTE确实是搞定这类层级关系的最佳工具。

先明确咱们的核心需求：为每个ID找到最新的有效活跃ID，不管它本身是活跃、曾被合并到其他ID，甚至和其他ID反复切换过活跃状态。

完整解决方案代码 #

-- 清理临时表（如果存在）
IF OBJECT_ID('tempdb..#main') IS NOT NULL DROP TABLE #main;
IF OBJECT_ID('tempdb..#merges') IS NOT NULL DROP TABLE #merges;

-- 1. 导入主ID表数据
SELECT id, current INTO #main FROM tb_ID;

-- 2. 预处理合并表：只保留每个非活跃ID的最新合并记录
SELECT 
    inactive_id, 
    active_id,
    Update_dt,
    ROW_NUMBER() OVER (PARTITION BY inactive_id ORDER BY Update_dt DESC) AS rn
INTO #merges
FROM tb_merges;

-- 删除旧的合并记录，只留最新的一条
DELETE FROM #merges WHERE rn > 1;

-- 3. 递归CTE追溯每个ID的最终活跃ID
WITH RecursiveMerge AS (
    -- 锚点成员：初始化每个ID的初始活跃ID
    SELECT 
        m.id,
        -- 逻辑：当前活跃的ID直接用自己；非活跃的先找最新合并的目标ID，没有的话暂时用自己
        CASE 
            WHEN m.current = 1 THEN m.id
            ELSE COALESCE(me.active_id, m.id)
        END AS latest_active_id,
        m.current AS current_status,
        -- 标记是否已找到最终活跃ID（避免无限递归）
        CASE 
            WHEN m.current = 1 THEN 1
            WHEN me.active_id IS NULL THEN 1
            ELSE 0
        END AS is_final
    FROM #main m
    LEFT JOIN #merges me ON m.id = me.inactive_id

    UNION ALL

    -- 递归成员：继续追溯当前活跃ID的合并状态，直到找到最终活跃ID
    SELECT 
        rm.id,
        CASE 
            WHEN m.current = 1 THEN m.id
            ELSE COALESCE(me.active_id, m.id)
        END AS latest_active_id,
        m.current AS current_status,
        CASE 
            WHEN m.current = 1 THEN 1
            WHEN me.active_id IS NULL THEN 1
            ELSE 0
        END AS is_final
    FROM RecursiveMerge rm
    JOIN #main m ON rm.latest_active_id = m.id
    LEFT JOIN #merges me ON m.id = me.inactive_id
    WHERE rm.is_final = 0 -- 只处理还没找到最终结果的记录
)

-- 4. 提取最终结果：每个ID只保留最新的追溯结果
SELECT 
    id,
    -- 按需调整：如果无活跃ID想返回自己，把ELSE NULL改成ELSE id
    CASE 
        WHEN current_status = 1 THEN latest_active_id
        ELSE NULL 
    END AS merged_to_id
FROM (
    SELECT 
        id,
        latest_active_id,
        current_status,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY is_final DESC) AS rn
    FROM RecursiveMerge
) t
WHERE rn = 1;

方案思路详解 #

1. 预处理合并表：

   • 因为同一个ID可能被多次合并，我们先通过ROW_NUMBER()按Update_dt降序排序，只保留每个非活跃ID的最新合并记录，避免处理过时的数据。

2. 递归CTE逻辑：

   • 锚点：从所有ID出发，先判断自身状态：活跃ID直接作为最终结果；非活跃ID先关联最新的合并记录，拿到初始的目标活跃ID。
   • 递归：如果当前找到的目标ID本身是非活跃的，继续追溯它的最新合并记录，直到找到一个**当前活跃（current=1）**的ID，或者没有更多合并记录为止。

3. 最终结果提取：

   • 用ROW_NUMBER()确保每个ID只取最后一次递归的结果，也就是最准确的最新活跃ID。

适配你的特殊场景 #

• 状态互换（如ID6和ID7交替）：因为我们始终取Update_dt最新的合并记录，递归会自动追溯到最后一次状态切换后的活跃ID。
• 无对应活跃ID：可以通过修改CASE语句，选择返回NULL或者ID本身。
• current=0的ID：递归会自动找到它最终指向的当前活跃ID，符合需求。

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