Oracle双变量递归查询：依据子节点所有父节点状态返回结果

1. 核心思路 #

• 用分层查询递归遍历指定子节点的所有父节点（同时匹配rule和key两个字段，确保节点唯一）
• 关联状态表获取每个父节点的状态
• 通过聚合函数判断所有父节点的状态：只要存在一个状态为0则返回0，全为1或无父节点则返回1

2. 使用CONNECT BY语法实现（推荐） #

这种方式贴合Oracle原生分层查询特性，且自带循环处理：

SELECT 
    CASE 
        WHEN MIN(s.status) IS NULL THEN 1  -- 无父节点时返回1
        WHEN MIN(s.status) = 1 THEN 1 
        ELSE 0 
    END AS final_status
FROM (
    -- 递归查询所有父节点，同时处理循环引用
    SELECT 
        r.parent_rule,
        r.parent_key,
        CONNECT_BY_ISCYCLE AS is_cycle  -- 标记是否出现循环
    FROM rules r
    -- 指定起始子节点：替换为你要查询的目标子节点的rule和key
    START WITH r.child_rule = :target_child_rule 
             AND r.child_key = :target_child_key
    -- 递归条件：同时匹配rule和key，NOCYCLE防止无限循环
    CONNECT BY NOCYCLE 
        PRIOR r.parent_rule = r.child_rule 
        AND PRIOR r.parent_key = r.child_key
) parent_nodes
-- 关联状态表获取父节点状态
LEFT JOIN status_table s 
    ON parent_nodes.parent_rule = s.rule 
    AND parent_nodes.parent_key = s.key
-- 排除循环节点（避免重复计算）
WHERE parent_nodes.is_cycle = 0;

代码说明 #

• START WITH：指定要查询的目标子节点，替换:target_child_rule和:target_child_key为实际业务值
• CONNECT BY NOCYCLE：NOCYCLE关键字自动处理规则表中的循环引用（比如A是B的父、B又是A的父这类情况），避免查询报错
• CONNECT_BY_ISCYCLE：标记当前行是否属于循环节点，后续过滤掉这些节点避免重复统计
• 聚合逻辑：MIN(s.status)会取所有父节点状态的最小值，只要有一个0则最小值为0；无父节点时MIN返回NULL，此时返回1

3. 使用递归CTE实现（更灵活） #

如果需要更复杂的逻辑扩展，可以用递归CTE：

WITH recursive_parents AS (
    -- 初始层：获取目标子节点的直接父节点
    SELECT 
        r.parent_rule,
        r.parent_key,
        s.status,
        -- 记录已访问的节点，防止循环（用字符串拼接rule和key）
        '/' || r.parent_rule || ',' || r.parent_key || '/' AS visited_nodes
    FROM rules r
    LEFT JOIN status_table s 
        ON r.parent_rule = s.rule 
        AND r.parent_key = s.key
    WHERE r.child_rule = :target_child_rule 
      AND r.child_key = :target_child_key
    
    UNION ALL
    
    -- 递归层：获取父节点的父节点
    SELECT 
        r.parent_rule,
        r.parent_key,
        s.status,
        -- 更新已访问节点列表
        rp.visited_nodes || r.parent_rule || ',' || r.parent_key || '/' AS visited_nodes
    FROM rules r
    LEFT JOIN status_table s 
        ON r.parent_rule = s.rule 
        AND r.parent_key = s.key
    JOIN recursive_parents rp 
        ON r.child_rule = rp.parent_rule 
        AND r.child_key = rp.parent_key
    -- 过滤已访问过的节点，避免循环
    WHERE rp.visited_nodes NOT LIKE '%/' || r.parent_rule || ',' || r.parent_key || '/%'
)
SELECT 
    CASE 
        WHEN MIN(status) IS NULL THEN 1
        WHEN MIN(status) = 1 THEN 1
        ELSE 0
    END AS final_status
FROM recursive_parents;

代码说明 #

• 递归CTE分为初始层和递归层，初始层获取直接父节点，递归层向上遍历更深层级的父节点
• 通过visited_nodes字段记录已访问的节点，手动避免循环引用
• 同样用MIN(status)判断最终状态

4. 关键注意事项 #

• 必须同时匹配rule和key：单独的rule无法唯一标识节点，会导致遍历错误或循环
• 循环处理：规则表可能存在循环引用，必须用NOCYCLE（CONNECT BY）或已访问节点过滤（递归CTE）避免无限递归
• 无父节点的情况：此时没有父节点需要检查，按需求返回1

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