如何将Oracle面包屑路径表转换为层级树形结构表？

核心思路 #

拆分面包屑路径提取所有层级节点，为重复节点分配唯一ID，建立父子节点关联，最终生成适配APEX树形视图的结构表。

假设表结构（适配常见采集场景） #

• 原表（BREADCRUMBS）：存储网站采集的面包屑数据
  • RECORD_ID：每条面包屑记录的唯一标识
  • PATH：面包屑路径字符串（例：首页/电子产品/手机/智能手机，分隔符可自定义）
• 目标表（TREE_STRUCTURE）：用于APEX树形展示
  • NODE_ID：节点唯一ID
  • PARENT_NODE_ID：父节点ID（根节点为NULL）
  • NODE_NAME：节点显示名称
  • NODE_LEVEL：节点层级（根节点为1）
  • SOURCE_RECORD_ID：关联原表记录ID（可选，用于追踪数据来源）

实现SQL #

-- 拆分路径节点 → 生成唯一节点ID → 建立父子关系 → 插入目标表
WITH split_nodes AS (
    -- 拆分每条面包屑路径，提取所有层级的节点及对应层级
    SELECT
        RECORD_ID,
        -- 替换分隔符：如果路径用>分隔，把[^/]+改成[^>]+
        REGEXP_SUBSTR(PATH, '[^/]+', 1, LEVEL) AS NODE_NAME,
        LEVEL AS NODE_LEVEL
    FROM BREADCRUMBS
    CONNECT BY 
        LEVEL <= REGEXP_COUNT(PATH, '/') + 1 -- 计算路径包含的节点数
        AND PRIOR RECORD_ID = RECORD_ID
        AND PRIOR SYS_GUID() IS NOT NULL -- 防止递归循环
),
unique_nodes AS (
    -- 为所有不重复的节点分配唯一ID（确保同一节点仅存在一条记录）
    SELECT
        ROW_NUMBER() OVER(ORDER BY NODE_NAME) AS NODE_ID,
        NODE_NAME
    FROM (
        SELECT DISTINCT NODE_NAME FROM split_nodes
    )
),
node_hierarchy AS (
    -- 为每个节点匹配父节点ID：同一面包屑记录中，上一层级的节点即为当前节点的父节点
    SELECT
        sn.RECORD_ID,
        un.NODE_ID,
        LAG(un.NODE_ID) OVER(PARTITION BY sn.RECORD_ID ORDER BY sn.NODE_LEVEL) AS PARENT_NODE_ID,
        sn.NODE_NAME,
        sn.NODE_LEVEL
    FROM split_nodes sn
    JOIN unique_nodes un ON sn.NODE_NAME = un.NODE_NAME
)
-- 插入目标表，去重避免重复节点
INSERT INTO TREE_STRUCTURE (NODE_ID, PARENT_NODE_ID, NODE_NAME, NODE_LEVEL, SOURCE_RECORD_ID)
SELECT DISTINCT
    NODE_ID,
    PARENT_NODE_ID,
    NODE_NAME,
    NODE_LEVEL,
    RECORD_ID
FROM node_hierarchy;

关键说明 #

1. 分隔符适配：如果面包屑路径使用>、|等其他分隔符，只需修改REGEXP_SUBSTR和REGEXP_COUNT中的分隔符即可。
2. 重复节点处理：通过unique_nodes CTE确保相同名称的节点仅分配一个NODE_ID，避免树形结构中出现重复节点。
3. 根节点处理：根节点（层级1）的PARENT_NODE_ID会因LAG函数返回NULL，符合APEX树形视图对根节点的要求。
4. 特殊场景兼容：自动处理空路径、单节点路径等特殊情况，不会出现递归错误。

APEX树形视图配置 #

将目标表作为数据源，在APEX树形组件中设置：

• 节点ID列：NODE_ID
• 父节点ID列：PARENT_NODE_ID
• 显示列：NODE_NAME

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