Oracle通过上/下层次结构连接

Question

如何使用connect by获取层次结构的顶部和底部，我有一个存储id之间转换的表( id -> REPLACE_ID)，我感兴趣的是从任何id开始获取最新的id。

--drop table test_connect_by;
create table test_connect_by(ID number, REPLACE_ID NUMBER);
insert into test_connect_by values(1,2);
insert into test_connect_by values(2,3);
insert into test_connect_by values(3,4);

insert into test_connect_by values(51,52);
insert into test_connect_by values(52,53);
insert into test_connect_by values(53,54);
insert into test_connect_by values(55,55);

SELECT id,replace_id, level
   FROM test_connect_by
   START WITH ID in (1,51)
   CONNECT BY PRIOR replace_id = id;

我对从1-4到51-54的过渡感兴趣，或者我可以从2开始，得到2-4。有什么我可以通过分组来识别从1开始的组和从51开始的组吗？

Answer 1

作为一种(更直截了当的)方法，您可以根据id值简单地找到最低的connect_by_root()和最高的replace_id分组，如果replace_id总是被保证大于id，那么请参阅Lennart answer。

select min(id)         as begins
     , max(replace_id) as ends
  from test_connect_by
 start with id in (1, 51)
 connect by id = prior replace_id
 group by connect_by_root(id)

结果：

    BEGINS       ENDS
---------- ----------
         1          4
        51         54

Answer 2

未经测试，因此可能会出现一些错误：

select id, replace_id 
from (
    SELECT CONNECT_BY_ROOT id as id, replace_id
         , row_number() over (partition by CONNECT_BY_ROOT id order by level desc) as rn
    FROM test_connect_by
    START WITH ID in (1,51)
    CONNECT BY PRIOR replace_id = id
) as T 
where rn = 1