如何使用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开头的组?
replace_id
可能小于id
,那么请查看Lennart的答案。 - Nick Krasnov