Oracle Connect By顶部/底部层次结构

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开头的组？

- Rodriguez

---

你想根据你的输入只有两个字符串：第一个是1-4，第二个是51-54吗？ - neshkeev

我需要两列：ID和LATEST_ID，从任何ID开始。 - Rodriguez

@Rodriguez 如果 replace_id 可能小于 id，那么请查看Lennart的答案。 - Nick Krasnov

Answer 1

未经测试，可能存在一些错误：

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

- Lennart - Slava Ukraini

---

@Nicholas，显然我们同时编辑了帖子，我希望在更新查询时包含了您的编辑精髓。 - Lennart - Slava Ukraini

Answer 2

作为其中一种简单的方法，你可以通过对connect_by_root()值进行分组，找到最小的id和最大的replace_id。如果保证replace_id始终大于id，那么就可以这样做。否则，请参考Lennart answer。请注意不要改变HTML标签。

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

- Nick Krasnov

---

你确定 replace_id > id 吗？ - Lennart - Slava Ukraini

@Lennart 不，那不是这种情况。 - Rodriguez

@Nicholas 我一直缺少connect_by_root功能，我不知道如何按什么键分组它 :) 谢谢！ - Rodriguez

如果replace_id > id不能得到保证，那么在“insert into test_connect_by values(4,0)”之后答案将是错误的。 - Lennart - Slava Ukraini

你想获取链中的最后一个“value”，你将得到最大值。例如，如果我正确理解了问题，那么你仍然会得到(1,4)，在这个插入之后的答案应该是(1,0)。 - Lennart - Slava Ukraini

@Lennart 是的，我同意你的观点。 - Nick Krasnov