SQL Server中匹配首个父级并更新business_table的parent_id

我来帮你梳理这个层级匹配更新的需求，并用SQL实现对应的逻辑。先把涉及的三张表结构和数据明确出来，再一步步拆解规则和实现方案：

1. 涉及的表结构与数据 #

business_table（待更新表） #

ref_ID     name    parent_id
-----------------------------
ABC-0001   Amb     NULL
PQR-899    boss    NULL
tgv-632    pick    NULL

parent_customer（层级关系表，ref_id=parent_id时为顶级父节点） #

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

match_table_CM（校验匹配的目标集合） #

id  main_id
--------------
1   opr-656
2   PQR-899
3   tgv-632
4   mnb-784

2. 核心更新规则 #

对business_table的每条记录，按以下逻辑更新parent_id：

• 用ref_ID匹配parent_customer的ref_id，拿到第一个父级parent_id
• 检查该父级是否在match_table_CM.main_id中：
  • 存在则直接更新business_table.parent_id为该值
  • 不存在则继续以当前父级为ref_id，去parent_customer找下一层父级，重复校验
• 直到找到第一个匹配match_table_CM的父级就停止；如果所有父级都不匹配，就用最后一个顶级父级（即ref_id=parent_id的节点）更新

举个实际例子：business_table里的ABC-0001，第一次匹配到父级opr-656，这个值在match_table_CM里存在，所以直接把ABC-0001的parent_id更新为opr-656。

3. SQL实现方案 #

我们可以用递归CTE来遍历每个节点的完整父级路径，再筛选出符合要求的目标父级。以下是适用于MySQL 8.0+、PostgreSQL等支持递归CTE的数据库的代码：

WITH RECURSIVE customer_hierarchy AS (
    -- 初始递归：从business_table的记录出发，获取第一个父级
    SELECT 
        bt.ref_ID,
        pc.parent_id AS current_parent,
        1 AS level
    FROM business_table bt
    JOIN parent_customer pc ON bt.ref_ID = pc.ref_id
    
    UNION ALL
    
    -- 递归遍历：继续查找当前父级的下一层父级，直到顶级节点
    SELECT 
        ch.ref_ID,
        pc.parent_id AS current_parent,
        ch.level + 1 AS level
    FROM customer_hierarchy ch
    JOIN parent_customer pc ON ch.current_parent = pc.ref_id
    WHERE ch.current_parent != pc.parent_id -- 到达顶级节点时停止递归
),
-- 为每个ref_ID筛选最优父级：优先匹配match_table_CM的项，层级越靠前越优先
ranked_parents AS (
    SELECT 
        ref_ID,
        current_parent,
        ROW_NUMBER() OVER (
            PARTITION BY ref_ID 
            ORDER BY 
                CASE WHEN mt.main_id IS NOT NULL THEN 0 ELSE 1 END, -- 匹配项排首位
                level -- 先找到的父级优先
        ) AS rn
    FROM customer_hierarchy ch
    LEFT JOIN match_table_CM mt ON ch.current_parent = mt.main_id
)
-- 执行更新操作
UPDATE business_table bt
JOIN ranked_parents rp ON bt.ref_ID = rp.ref_ID
SET bt.parent_id = rp.current_parent
WHERE rp.rn = 1;

代码逻辑说明 #

1. customer_hierarchy递归CTE：遍历每个business_table记录的所有父级路径，直到遇到顶级节点（current_parent = parent_id）
2. ranked_parents：对每个ref_ID的父级进行排序，把在match_table_CM中存在的项排在最前面，同时按层级顺序（先找到的父级优先），最后取排序后的第一条记录（rn=1）作为目标父级
3. UPDATE语句：用筛选出的目标父级更新business_table的parent_id

执行完成后，business_table的最终结果会是：

ref_ID     name    parent_id
-----------------------------
ABC-0001   Amb     opr-656
PQR-899    boss    PQR-899
tgv-632    pick    mnb-784

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