sql - Oracle Connect 按顶层/底层层次结构

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

作为一种(更直接的)方法，您可以根据 connect_by_root() 值简单地找到最低的 id 和最高的 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