基于多表层级匹配更新business_table的parent_id技术问询

首先先明确我们的三个表数据（方便后续参考）：

business_table 初始数据 #

ref_ID     name  parent_id
-----------------------------
ABC-0001   Amb   NULL
PQR-899    boss  NULL
tgv-632    pick  NULL
yyy-888    xyz   NULL
kkk-456    ued   NULL

parent_customer 表（层级关系） #

ID  ref_id     parent_id
---------------------------
1   ABC-0001   opr-656
2   opr-656    ttK-668
3   ttK-668    ttK-668
4   PQR-899    PQR-899
5   kkk-565    AJY-567
6   AJY-567    UXO-989
7   UXO-989    tgv-632
8   tgv-632    mnb-784
9   mnb-784    qwe-525
10  qwe-525    qwe-525
11  kkk-456    jjj-888

match_table_CM 表（目标匹配ID） #

id  main_id
--------------
1   ttK-668
2   PQR-899
3   tgv-632
4   mnb-784

需求回顾 #

我们需要更新business_table的parent_id字段，核心逻辑是：

• 先把business_table的ref_id和parent_customer的ref_id匹配，拿到对应父节点ID
• 检查这个父节点ID是否在match_table_CM的main_id里
• 如果没匹配上，就把这个父节点ID当成新的ref_id，继续在parent_customer里找它的父节点，重复匹配逻辑
• 直到找到匹配match_table_CM的ID，或者走到层级的最终父节点（ref_id和parent_id相同的记录），最后用这个节点ID更新business_table的parent_id
• 对于business_table里在parent_customer找不到匹配记录的（比如yyy-888），保持parent_id为NULL

实现SQL代码 #

这里用**递归CTE（公共表表达式）**来处理层级遍历和匹配逻辑，是最适合这类树形层级问题的方案：

WITH RECURSIVE hierarchy_match AS (
    -- 初始步骤：获取business_table每条记录的初始父节点，同时标记是否匹配目标表
    SELECT 
        bt.ref_ID,
        pc.parent_id AS current_id,
        CASE WHEN mt.main_id IS NOT NULL THEN 1 ELSE 0 END AS is_matched
    FROM business_table bt
    LEFT JOIN parent_customer pc ON bt.ref_ID = pc.ref_id
    LEFT JOIN match_table_CM mt ON pc.parent_id = mt.main_id

    UNION ALL

    -- 递归步骤：未匹配到目标ID时，继续向上查找父节点
    SELECT 
        hm.ref_ID,
        pc.parent_id AS current_id,
        CASE WHEN mt.main_id IS NOT NULL THEN 1 ELSE 0 END AS is_matched
    FROM hierarchy_match hm
    JOIN parent_customer pc ON hm.current_id = pc.ref_id
    LEFT JOIN match_table_CM mt ON pc.parent_id = mt.main_id
    -- 终止条件：要么已经匹配到目标，要么到达最终父节点（自身是自身的父节点）
    WHERE hm.is_matched = 0 AND pc.ref_id != pc.parent_id
),
-- 筛选每个ref_ID的最终目标ID（取最后一次递归的有效结果）
final_target AS (
    SELECT 
        ref_ID,
        current_id AS target_parent_id
    FROM (
        SELECT 
            ref_ID,
            current_id,
            -- 给每个ref_ID的递归记录排序，最后一条就是我们要的最终节点
            ROW_NUMBER() OVER (PARTITION BY ref_ID ORDER BY (SELECT NULL) DESC) AS rn
        FROM hierarchy_match
        -- 只保留匹配到目标的记录，或者最终父节点记录
        WHERE is_matched = 1 OR (current_id IN (SELECT ref_id FROM parent_customer WHERE ref_id = parent_id))
    ) t
    WHERE rn = 1
)
-- 执行更新操作
UPDATE business_table bt
SET parent_id = ft.target_parent_id
FROM final_target ft
WHERE bt.ref_ID = ft.ref_ID;

代码解释 #

1. 递归CTE hierarchy_match：

   • 初始分支：完成第一步匹配，拿到每条业务记录的初始父节点，同时标记是否命中目标表
   • 递归分支：针对未命中的节点，持续向上遍历父节点，直到满足终止条件

2. final_target CTE：

   • 对每个业务记录的递归结果排序，取最后一条有效记录（要么是命中目标的ID，要么是层级最顶端的父节点ID）

3. 更新语句：

   • 将筛选出的最终目标ID，批量更新到business_table的parent_id字段

验证结果 #

执行完上述SQL后，business_table会完全符合预期输出：

ref_ID     name  parent_id
-----------------------------
ABC-0001   Amb   ttK-668
PQR-899    boss  PQR-899
tgv-632    pick  qwe-525
yyy-888    xyz   NULL
kkk-456    ued   jjj-888

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