Oracle SQL查询新增六级WBS_ID列需求求助

原查询SQL #

SELECT 
    wbs.wbs_id, 
    wbs.wbs_name, 
    wbs.parent_wbs_id, 
    LEVEL AS hierarchy_level, 
    wbs.proj_id As Proj_id
FROM 
    PROJWBS wbs
LEFT JOIN 
    task b ON wbs.wbs_id = b.wbs_id  -- Join with TASK table based on WBS ID
START WITH 
    wbs.parent_wbs_id IS NULL  -- Start from the root (WBS with no parent)
CONNECT BY 
    PRIOR wbs.wbs_id = wbs.parent_wbs_id  -- Connect child WBS elements to parent WBS
ORDER SIBLINGS BY 
    wbs.wbs_name  -- Optional: Order siblings by WBS name

需求说明 #

新增一列sixth_wbs_id，规则如下：

• 当hierarchy_level ≥ 6时，该列值为当前节点所在层级链中第6级的wbs_id
• 当hierarchy_level < 6时，该列值为NULL

示例效果 #

修改后的查询语句 #

SELECT 
    wbs.wbs_id, 
    wbs.wbs_name, 
    wbs.parent_wbs_id, 
    LEVEL AS hierarchy_level, 
    wbs.proj_id AS proj_id,
    -- 当层级≥6时，提取路径中第6级的wbs_id，否则返回NULL
    CASE 
        WHEN LEVEL >= 6 THEN
            REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(wbs.wbs_id, ','), ',([^,]+)', 1, 6, NULL, 1)
        ELSE 
            NULL 
    END AS sixth_wbs_id
FROM 
    PROJWBS wbs
LEFT JOIN 
    task b ON wbs.wbs_id = b.wbs_id
START WITH 
    wbs.parent_wbs_id IS NULL
CONNECT BY 
    PRIOR wbs.wbs_id = wbs.parent_wbs_id
ORDER SIBLINGS BY 
    wbs.wbs_name

说明 #

• SYS_CONNECT_BY_PATH(wbs.wbs_id, ',')：生成从根节点到当前节点的WBS ID路径字符串，格式为,id1,id2,...,idN
• REGEXP_SUBSTR(..., ',([^,]+)', 1, 6, NULL, 1)：正则提取路径中第6个分隔后的ID值，作为第六级WBS ID
• 如果你的wbs_id包含逗号，可将分隔符替换为其他特殊字符（比如'|'），同时修改正则表达式中的分隔符匹配规则

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