Sql Oracle中的分层查询

这是一个可以得到您预期结果的查询

WITH tmp AS
(
    SELECT 12345 AS id,     TO_DATE('07-Aug-15', 'DD/MON/YY') AS Start_date,    TO_DATE('07-Aug-65', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('22-Aug-15', 'DD/MON/YY') AS Start_date,    TO_DATE('01-Jan-16', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('24-Mar-16', 'DD/MON/YY') AS Start_date,    TO_DATE('23-Mar-66', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('06-Jul-16', 'DD/MON/YY') AS Start_date,    TO_DATE('31-Dec-17', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 12345 AS id,     TO_DATE('31-Dec-16', 'DD/MON/YY') AS Start_date,    TO_DATE('31-Dec-41', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    
    SELECT 4662828 AS id,   TO_DATE('22-Aug-15', 'DD/MON/YY') AS Start_date,    TO_DATE('22-Dec-15', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 4662828 AS id,   TO_DATE('01-Jan-16', 'DD/MON/YY') AS Start_date,    TO_DATE('01-Aug-18', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 4662828 AS id,   TO_DATE('10-Jun-17', 'DD/MON/YY') AS Start_date,    TO_DATE('31-Dec-18', 'DD/MON/YY') AS End_date FROM dual UNION ALL
    SELECT 4662828 AS id,   TO_DATE('01-Dec-18', 'DD/MON/YY') AS Start_date,    TO_DATE('04-Dec-72', 'DD/MON/YY') AS End_date FROM dual 
)
, tmp1 AS
(
    SELECT 
        CONNECT_BY_ROOT t.start_date AS start_date,
        t.id,
        t.start_date AS start_date1,
        t.end_date,
        level AS lvl
    FROM tmp t
    CONNECT BY PRIOR id = id AND PRIOR start_date <> start_date AND PRIOR end_date <> end_date 
        AND PRIOR end_date BETWEEN start_date AND end_date
)
, tmp2 AS 
(
    SELECT *
    FROM tmp1
    WHERE (start_date, id, lvl) IN
        (
            SELECT start_date, id, MAX(lvl)
            FROM tmp1
            GROUP BY start_date, id
        )
)
SELECT id,
    MIN(start_date) AS start_date,
    end_date
FROM tmp2
GROUP BY id, end_date
ORDER BY id, start_date;

fiddle