求助：Oracle中基于Parent/Child表的指定类型层级SQL查询实现

问题背景 #

现有两个Oracle表：

• parent表：字段包括Id（主键）、type（类型标识）、name（名称）
• child表：字段包括child_id（子节点ID）、parent_id（父节点ID）

业务存在固定类型层级规则：A → B → C → D，每个父类型仅允许关联下一层级的子类型（如A的子节点只能是B，B的子节点只能是C，以此类推）。

需求：给定任意类型（A/B/C/D）的一个节点ID，遍历查询所有符合层级规则的父-子关联数据，直到遍历至D层级为止。

示例数据 #

parent表

child表

预期输出 #

注：若存在同层级的多个符合规则子节点，比如A类型下还有其他B类型节点（如ID=66、67），这些记录也会被包含在结果中。

实现SQL语句 #

使用Oracle递归CTE（Common Table Expression）实现层级遍历与规则过滤，SQL代码如下：

WITH hierarchy AS (
    -- 锚点查询：获取起始节点的基础信息
    SELECT 
        p.Id AS parent_id,
        p.type AS parent_type,
        CAST(NULL AS NUMBER) AS child_id,
        CAST(NULL AS VARCHAR2(10)) AS child_type
    FROM parent p
    WHERE p.Id = :start_id -- 替换为目标起始ID，例如1
    UNION ALL
    -- 递归查询：逐层获取符合层级规则的子节点
    SELECT 
        h.parent_id,
        h.parent_type,
        c.child_id,
        p.type AS child_type
    FROM hierarchy h
    JOIN child c ON h.parent_id = c.parent_id
    JOIN parent p ON c.child_id = p.Id
    -- 核心：过滤符合层级规则的子节点
    WHERE 
        (h.parent_type = 'A' AND p.type = 'B')
        OR (h.parent_type = 'B' AND p.type = 'C')
        OR (h.parent_type = 'C' AND p.type = 'D')
)
-- 提取有效父-子关联记录
SELECT 
    parent_id AS parent,
    child_id AS child
FROM hierarchy
WHERE child_id IS NOT NULL
ORDER BY parent_id, child_id;

代码说明 #

1. 锚点成员：先定位到给定的起始节点，获取其ID和类型信息，作为递归的起点。
2. 递归成员：
   • 通过child表关联当前节点的所有子节点ID
   • 关联parent表获取子节点的类型
   • 通过WHERE条件严格过滤，确保子节点类型符合A→B→C→D的层级规则
3. 结果提取：排除锚点的空记录，仅保留有效的父-子ID对，并按父ID、子ID排序。

效果验证 #

以起始ID=1（A类型）为例：

• 第一步：找到child表中parent_id=1的所有子节点，筛选出类型为B的节点（ID=2），得到记录(1,2)
• 第二步：以ID=2（B类型）为父节点，找到child表中parent_id=2的子节点，筛选出类型为C的节点（ID=3），得到记录(2,3)
• 若ID=3存在类型为D的子节点，会继续遍历并输出对应记录，直到无符合规则的子节点或到达D层级

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