Oracle CTE递归实现PCB行业的叠层关系

1、需求背景，出货报告要实现叠板假层的处理，需求如下

表ID,layer,MEDIUM数据如下
第一种情况，layer有K的

实现layer有K1的，L3-L4,并合计 MEDIUM列等于30，layer有K2的，L7-L8,并合计 MEDIUM列等于21

第二种情况，layer有K的

实现layer有K的，L3-L4,并合计 MEDIUM列等于50=(20+10+20)

处理思路

该方案通过递归CTE构建完整路径链，自动处理单K节点（如L3-K1-L4）和多K连续节点（如L3-K1-K2-L4）两种情况，输出合并后的层级路径及MEDIUM总和。

执行示例：

第一种情况输出：

L3-L4 | 30
L7-L8 | 21

第二种情况输出：

L3-L4 | 50 (20+10+20)

关键特性：

动态节点识别：自动检测所有K节点（K1/K2等）15
路径完整性检查：确保合并后的路径始终以非K节点结尾68
最短路径优先：当存在多条合并路径时选择最短链路

WITH 表达式

WITH split_data AS (
    SELECT 
        ID,
        layer,
        MEDIUM,
        REGEXP_SUBSTR(layer, '[^-]+') AS start_node,
        REGEXP_SUBSTR(layer, '[^-]+$') AS end_node
    FROM layer_data
),
path_chains AS (
    -- 基础路径（起始节点非K的路径）
    SELECT 
        ID, layer, MEDIUM,
        start_node AS chain_start,
        end_node AS chain_end,
        CAST(layer AS VARCHAR2(4000)) AS full_path,
        MEDIUM AS total_medium,
        1 AS path_level
    FROM split_data 
    WHERE start_node NOT LIKE 'K%'
    
    UNION ALL
    
    -- 递归连接后续路径
    SELECT 
        d.ID, d.layer, d.MEDIUM,
        p.chain_start,
        d.end_node AS chain_end,
        p.full_path || '→' || d.layer,
        p.total_medium + d.MEDIUM,
        p.path_level + 1
    FROM path_chains p
    JOIN split_data d ON p.chain_end = d.start_node
    WHERE p.chain_end NOT LIKE 'L%'  -- 仅连接K节点或中间节点
),
k_merged AS (
    SELECT 
        chain_start || '-' || chain_end AS combined_layer,
        total_medium,
        ROW_NUMBER() OVER (PARTITION BY chain_start, chain_end ORDER BY path_level) AS rn
    FROM path_chains
    WHERE chain_end LIKE 'L%'  -- 最终结束节点需为非K节点
    AND full_path LIKE '%K%'   -- 必须包含K节点
)
SELECT 
    combined_layer,
    total_medium
FROM k_merged
WHERE rn = 1  -- 取最短路径合并结果
ORDER BY combined_layer;