TSQL 查询父子层级关系表中的无效循环引用映射实现方案

• 第一步：校验规则1（仅自映射无效），筛选出父节点只有「自身映射」一条记录、没有其他子节点映射的无效数据
• 第二步：用递归CTE遍历所有父子链路，同时拼接路径字符串避免重复匹配，一旦发现链路中出现重复节点即可判定为循环，覆盖规则2（直接反向引用）和规则3（间接反向引用）的场景
• 最后合并两类无效记录，附带具体无效原因和循环路径输出

;WITH
-- 规则1：仅映射自身的无效记录
Rule1_Invalid AS (
    SELECT ParentId, ChildId, 'INVALID : 父节点仅映射自身' AS InvalidReason
    FROM @Data d1
    WHERE ParentId = ChildId
    AND NOT EXISTS (
        SELECT 1 FROM @Data d2 
        WHERE d2.ParentId = d1.ParentId 
        AND d2.ChildId <> d2.ParentId
    )
),
-- 递归遍历所有链路，记录路径避免无限递归
RecursiveCTE AS (
    SELECT 
        ParentId, 
        ChildId,
        CAST(CONCAT('|', ParentId, '|', ChildId, '|') AS VARCHAR(MAX)) AS Path,
        1 AS Level
    FROM @Data
    WHERE ParentId <> ChildId

    UNION ALL

    SELECT 
        r.ParentId,
        t.ChildId,
        CAST(CONCAT(r.Path, t.ChildId, '|') AS VARCHAR(MAX)),
        r.Level + 1
    FROM RecursiveCTE r
    JOIN @Data t ON r.ChildId = t.ParentId
    WHERE t.ParentId <> t.ChildId
    AND CHARINDEX(CONCAT('|', t.ChildId, '|'), r.Path) = 0
),
-- 规则2、3：存在循环引用的无效记录
Rule2_3_Invalid AS (
    SELECT DISTINCT
        d.ParentId,
        d.ChildId,
        CONCAT('INVALID : 存在循环路径 ', REPLACE(TRIM('|' FROM r.Path), '|', ' > ')) AS InvalidReason
    FROM @Data d
    JOIN RecursiveCTE r 
        ON d.ParentId = r.ParentId 
        AND CHARINDEX(CONCAT('|', d.ChildId, '|'), r.Path) > 0
    WHERE CHARINDEX(CONCAT('|', d.ParentId, '|'), STUFF(r.Path, 1, CHARINDEX(CONCAT('|', d.ChildId, '|'), r.Path), '')) > 0
)
-- 合并输出所有无效记录
SELECT ParentId, ChildId, InvalidReason FROM Rule1_Invalid
UNION ALL
SELECT ParentId, ChildId, InvalidReason FROM Rule2_3_Invalid
OPTION (MAXRECURSION 100);

运行后会返回测试数据中标注的全部8条无效记录，每条记录都附带对应的无效原因和完整循环路径，可直接匹配注释中标记的无效场景。

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