1、需求背景，出貨報告要實現疊板假層的處理，需求如下

表ID,layer,MEDIUM數據如下
第一種情況，layer有K的

ID	layer	MEDIUM
1	L1-L2	30
2	L2-L3	40
3	L3-K1	20
4	K1-L4	10
5	L4-L5	20
6	L5-L6	30
7	L7-K2	10
8	K2-L8	11
9	L8-L9	10
10	L9-L10	30

實現layer有K1的，L3-L4,并合計 MEDIUM列等于30，layer有K2的，L7-L8,并合計 MEDIUM列等于21

layer	MEDIUM
L1-L2	30
L2-L3	40
L3-L4	30
L4-L5	20
L5-L6	30
L7-L8	21
L8-L9	10
L9-L10	30

第二種情況，layer有K的

ID	layer	MEDIUM
1	L1-L2	30
2	L2-L3	40
3	L3-K1	20
4	K1-K2	10
5	K2-L4	20
6	L4-L5	30
7	L5-L6	10

實現layer有K的，L3-L4,并合計 MEDIUM列等于50=(20+10+20)

layer	MEDIUM
L1-L2	30
L2-L3	40
L3-L4	50
L4-L5	30
L5-L6	10

處理思路

該方案通過遞歸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_nodeFROM 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_levelFROM 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 + 1FROM path_chains pJOIN split_data d ON p.chain_end = d.start_nodeWHERE 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 rnFROM path_chainsWHERE 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;