如何在Oracle SQL中将层级路径拆分为父子列？

针对给定的层级路径字符串 pcmdocs/Marquette/10600551 - Track Replacement/CDA-Contract Documentation-Administration/folder1，可以通过以下SQL语句将其拆分为包含当前节点和父节点的层级结构：

WITH path_data AS (
    -- 存储原始层级路径
    SELECT 'pcmdocs/Marquette/10600551 - Track Replacement/CDA-Contract Documentation-Administration/folder1' AS full_path FROM dual
),
nodes AS (
    -- 拆分路径节点并关联父节点
    SELECT 
        LEVEL AS node_level,
        REGEXP_SUBSTR(full_path, '[^/]+', 1, LEVEL) AS node_name,
        LAG(REGEXP_SUBSTR(full_path, '[^/]+', 1, LEVEL)) OVER (ORDER BY LEVEL) AS parent_node
    FROM path_data
    CONNECT BY REGEXP_SUBSTR(full_path, '[^/]+', 1, LEVEL) IS NOT NULL
)
-- 输出最终的父子结构，根节点父节点设为NULL
SELECT 
    node_name AS "当前节点",
    CASE WHEN node_level = 1 THEN NULL ELSE parent_node END AS "父节点"
FROM nodes;

逻辑说明 #

1. path_data：用CTE存储原始路径，方便后续复用；
2. nodes：通过CONNECT BY循环调用REGEXP_SUBSTR拆分路径中的每个节点，同时用LAG函数获取当前节点的上一级节点作为父节点；
3. 最终查询处理根节点的父节点为NULL，符合层级结构的常规展示。

执行结果 #

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