如何用SQL递归CTE获取每个节点首个Condition=0的父节点？

核心思路 #

递归CTE需要分两步处理，重点是让递归持续到找到目标节点为止：

• 锚点成员：先提取所有节点的初始关联信息（自身ID、直接父节点ID、父节点的Condition值），作为递归起点。
• 递归成员：仅对还未找到目标的记录继续递归——也就是当前追踪的父节点Condition=1时，继续向上查找它的父节点，直到找到Condition=0的节点，或者无父节点为止。

完整SQL实现（以SQL Server为例） #

WITH RecursiveParent AS (
    -- 锚点成员：初始化每个节点的直接父节点信息
    SELECT 
        Id = t.Id,
        TargetParentId = t.[Parent Id],
        TargetParentCondition = p.Condition
    FROM YourTable t
    LEFT JOIN YourTable p ON t.[Parent Id] = p.Id

    UNION ALL

    -- 递归成员：仅当当前父节点不满足条件时，继续向上追踪
    SELECT 
        r.Id,
        TargetParentId = p.[Parent Id],
        TargetParentCondition = pp.Condition
    FROM RecursiveParent r
    JOIN YourTable p ON r.TargetParentId = p.Id
    LEFT JOIN YourTable pp ON p.[Parent Id] = pp.Id
    WHERE r.TargetParentCondition = 1 -- 只对还没找到目标的记录继续递归
)
-- 提取每个ID的首个符合条件的父节点，无匹配则返回NULL
SELECT 
    Id,
    FirstNonCompliantParentId = CASE 
        WHEN TargetParentCondition = 0 THEN TargetParentId 
        ELSE NULL 
    END
FROM (
    SELECT 
        Id,
        TargetParentId,
        TargetParentCondition,
        -- 按递归顺序取第一条匹配记录（最靠近当前节点的目标父节点）
        ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT 0)) AS rn
    FROM RecursiveParent
    WHERE TargetParentCondition = 0 OR TargetParentId IS NULL -- 只保留有效结果
) sub
WHERE rn = 1;

代码说明 #

1. 锚点成员：关联节点与直接父节点，拿到初始的父节点状态，为递归做准备。
2. 递归成员：通过WHERE r.TargetParentCondition = 1过滤，只对还没找到目标的记录继续向上查找，避免无效递归。
3. 最终筛选：用ROW_NUMBER()按ID分组，取递归过程中第一个出现的Condition=0的父节点；如果所有上级都满足Condition=1，最终返回NULL。

你的递归CTE问题修正 #

之前的CTE只返回直接父节点，大概率是没加仅对Condition=1的父节点继续递归的过滤条件，导致递归只执行了一次就终止。加上WHERE r.TargetParentCondition = 1后，就能持续向上追踪直到找到目标节点。

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