多表递归查询：带条件提取父/子信息及描述

以下是基于SQL递归CTE实现的方案，可满足提取父系层级并按规则替换Description的需求：

1. 递归CTE获取父系层级及目标Description #

WITH RecursiveHierarchy AS (
    -- 初始节点：关联Table3的记录，同时标记是否找到含>的Name
    SELECT
        t3.id AS NodeID,
        t3.name AS NodeName,
        CAST(NULL AS VARCHAR(255)) AS ParentNodeID,
        t2.Description AS TargetDesc,
        CASE WHEN t2.Name LIKE '%>%' THEN 1 ELSE 0 END AS FoundMatch
    FROM Table3 t3
    LEFT JOIN Table2 t2 ON t3.id = t2.IDFromTable3

    UNION ALL

    -- 递归向上遍历父节点，仅在未找到匹配时更新TargetDesc
    SELECT
        parent_t3.id AS NodeID,
        parent_t3.name AS NodeName,
        rh.NodeID AS ParentNodeID,
        CASE 
            WHEN rh.FoundMatch = 0 AND parent_t2.Name LIKE '%>%' THEN parent_t2.Description
            ELSE rh.TargetDesc
        END AS TargetDesc,
        CASE 
            WHEN rh.FoundMatch = 1 THEN 1
            WHEN parent_t2.Name LIKE '%>%' THEN 1
            ELSE 0
        END AS FoundMatch
    FROM RecursiveHierarchy rh
    JOIN Table3 parent_t3 ON rh.NodeID = parent_t3.parent_id -- 假设Table3有parent_id字段关联父节点
    LEFT JOIN Table2 parent_t2 ON parent_t3.id = parent_t2.IDFromTable3
    WHERE rh.FoundMatch = 0 -- 已找到匹配则停止递归
)

2. 关联Table1获取最终结果 #

SELECT
    t1.id AS Table1ID,
    t1.Name AS Table1Name,
    -- 优先使用递归找到的TargetDesc，无则保留原Description
    COALESCE(rh.TargetDesc, t1.Description) AS FinalDescription,
    -- 拼接完整父系层级（可根据需求调整格式）
    STRING_AGG(rh.NodeName, ' > ') WITHIN GROUP (ORDER BY rh.ParentNodeID DESC) AS FullHierarchy
FROM Table1 t1
LEFT JOIN RecursiveHierarchy rh ON t1.LocationIDFromTable3 = rh.NodeID
-- 取每个节点的最终递归结果（即层级最顶层或找到匹配后的结果）
WHERE rh.ParentNodeID IS NULL OR rh.FoundMatch = 1
GROUP BY t1.id, t1.Name, COALESCE(rh.TargetDesc, t1.Description)

关键逻辑说明 #

• 递归CTE中通过FoundMatch标记是否已找到第一个含>的Table2记录，一旦标记为1则停止后续递归，确保只取第一个匹配的Description
• 初始节点直接关联Table3和Table2，递归时仅在未找到匹配的情况下更新TargetDesc
• 主查询通过COALESCE实现Description的替换逻辑，无匹配时保留Table1原字段
• STRING_AGG用于拼接父系层级，可根据实际需求调整分隔符和排序方式

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