Oracle 12c中构建家族层级结构需求求解

嘿，这个需求我熟！在Oracle 12c里有两种常用的方法可以生成你要的层级路径，直接就能套用，下面给你详细说明：

方法一：递归CTE（推荐，逻辑清晰） #

递归CTE是Oracle 11g及以后支持的特性，用起来直观易懂，适合处理这种层级遍历场景：

WITH family_hierarchy AS (
    -- 第一步：定位每个家庭的根节点（Ref_Parent为空的记录）
    SELECT 
        Family_ID,
        Seq_ID,
        Name,
        Ref_Parent,
        Name AS Hierarchy
    FROM 
        your_table_name  -- 替换成你的实际表名
    WHERE 
        Ref_Parent IS NULL

    UNION ALL

    -- 第二步：递归遍历子节点，拼接层级路径
    SELECT 
        child.Family_ID,
        child.Seq_ID,
        child.Name,
        child.Ref_Parent,
        parent.Hierarchy || ' > ' || child.Name AS Hierarchy
    FROM 
        your_table_name child
    JOIN 
        family_hierarchy parent 
        ON child.Family_ID = parent.Family_ID  -- 确保在同一个家庭内递归
        AND child.Ref_Parent = parent.Seq_ID   -- 关联父节点的Seq_ID
)
-- 最终输出结果，按家庭和节点顺序排序
SELECT 
    Family_ID,
    Seq_ID,
    Name,
    Ref_Parent,
    Hierarchy
FROM 
    family_hierarchy
ORDER BY 
    Family_ID, Seq_ID;

关键说明： #

• 递归CTE分为两部分：起始查询（根节点）和递归查询（子节点关联父节点）
• 用Family_ID做关联条件，避免不同家庭的节点被错误拼接
• 根节点的Hierarchy直接用自身Name，子节点则把父节点的路径和自己的Name用>连接

方法二：传统CONNECT BY语法（适合习惯Oracle旧语法的场景） #

如果你更熟悉Oracle的传统层级查询语法，也可以用SYS_CONNECT_BY_PATH函数快速生成路径：

SELECT 
    Family_ID,
    Seq_ID,
    Name,
    Ref_Parent,
    -- 生成带前缀的路径，再用LTRIM去掉开头多余的' > '
    LTRIM(SYS_CONNECT_BY_PATH(Name, ' > '), ' > ') AS Hierarchy
FROM 
    your_table_name  -- 替换成你的实际表名
START WITH Ref_Parent IS NULL  -- 指定根节点的起始条件
CONNECT BY 
    PRIOR Seq_ID = Ref_Parent  -- 关联父节点和子节点
    AND PRIOR Family_ID = Family_ID  -- 限制在同一个家庭内递归
ORDER BY 
    Family_ID, Seq_ID;

关键说明： #

• SYS_CONNECT_BY_PATH函数会自动按层级拼接路径，第一个参数是要拼接的字段，第二个是分隔符
• 因为函数会在路径开头多一个分隔符，所以用LTRIM去掉开头的>
• START WITH和CONNECT BY组合实现层级遍历，PRIOR关键字表示父节点的字段

这两种方法都能完美生成你期望的输出结果，记得把your_table_name替换成你实际使用的表名哦！

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