Oracle层次查询转换：将START WITH/CONNECT BY语句转为递归子查询分解

WITH recursive_hierarchy AS (
    -- 锚点成员：对应原查询START WITH的起始节点
    SELECT 
        t1.id,
        t1.parent_id,
        t1.child_index
    FROM table1 t1
    JOIN table2 t2 
        ON t1.version_id = t2.id
    WHERE 
        t1.baseline_date = TRIM(TO_DATE('2015-05-26', 'yyyy-mm-dd'))
        AND t2.entry_date = t1.baseline_date
        AND t1.parent_id = 101015  -- 等价于原START WITH的子查询条件
    
    UNION ALL
    
    -- 递归成员：遍历子节点，对应原CONNECT BY逻辑
    SELECT 
        t1.id,
        t1.parent_id,
        t1.child_index
    FROM table1 t1
    JOIN table2 t2 
        ON t1.version_id = t2.id
    JOIN recursive_hierarchy rh 
        ON rh.id = t1.parent_id  -- 对应原CONNECT BY PRIOR t1.id = t1.parent_id
    WHERE 
        t1.baseline_date = TRIM(TO_DATE('2015-05-26', 'yyyy-mm-dd'))
        AND t2.entry_date = t1.baseline_date
)
SELECT id
FROM recursive_hierarchy
ORDER BY child_index;  -- 对应原ORDER SIBLINGS BY t1.child_index

转换说明 #

• 锚点成员直接筛选层次查询的起始节点，用t1.parent_id = 101015替代原查询START WITH中的子查询，逻辑完全等价。
• 递归成员通过关联递归CTE自身，实现原CONNECT BY的层次遍历逻辑，即以上一层节点的id匹配当前节点的parent_id，向下遍历子树。
• 原查询的表关联、日期筛选条件需在锚点和递归成员中同时保留，确保仅处理符合要求的记录。
• 若需要更精准的层次同级排序，可在递归CTE中添加层次路径字段（类似SYS_CONNECT_BY_PATH的实现），进一步细化排序规则。

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