Sql Oracle分层查询两条路径在末尾合并为一条

在sqlfiddle创建模式:

我想用Oracle中的模式查询一个层次 struct 表.模式和示例数据如下:

CREATE TABLE mappings 
(
    from_name varchar(20),
    to_name varchar(20),
    start_date date
);

INSERT INTO mappings VALUES ('node 1', 'node 1-2', date'2023-1-1');
INSERT INTO mappings VALUES ('node 1-2', 'node 1-3', date'2023-2-1');
INSERT INTO mappings VALUES ('node 1-3', 'Final Node 1', date'2023-3-1');
INSERT INTO mappings VALUES ('node 2', 'Final Node 2', date'2023-1-15');
INSERT INTO mappings VALUES ('node 3', 'Node 3-1', date'2023-2-1');
INSERT INTO mappings VALUES ('node 3-1', 'Final Node 1', date'2023-2-15');

该数据表示具有时间段的3种不同映射路径:

• 路径1: node 1-&gt； node 1-2-&gt； node 1-3-&gt；最终 node 1
• 路径2: node 2-&gt；最终 node 2
• 路径3: node 3-&gt； node 3-1-&gt；最终 node 1

请注意，路径1和路径3在末尾合并

我想要生成的输出是:

我在Oracle中的代码:

WITH map AS 
(
    SELECT 
       connect_by_root(from_name) AS begin_node,
       (to_name) AS end_node,
       connect_by_root(start_date) AS start_date,
       LEVEL
   FROM 
       mappings
   WHERE 
       connect_by_isleaf = 1 
   CONNECT BY nocycle from_name = PRIOR to_name
)
SELECT 
    m.begin_node,
    m.end_node,
    start_date,
    LEAD(start_date - 1, 1) OVER (PARTITION BY end_node ORDER BY start_date) AS end_date
FROM 
    map m

上面的代码似乎在大多数情况下都可以工作，但在某些情况下end_date不正确.它从另一条路径拾取错误的结束日期，因为路径1和路径3在末尾合并.