展开全部
简单做了几条数据,我这应该是没问62616964757a686964616fe78988e69d8331333332643337题,只是不知道拿到你那会怎么样,有问题直接回复
测试数据create table test
(parent_id int,
id int,
is_rp int,
name varchar2(10));
insert into test values (null,1,1,'aa');
insert into test values (1,2,1,'bb');
insert into test values (1,3,2,'cc');
insert into test values (2,4,1,'dd');
insert into test values (3,5,2,'ee');
insert into test values (4,6,2,'ff');
commit;
执行with t as
(select test.*,level as lv from test
start with id = 6 --这个地方为输入id
connect by prior parent_id=id)
select name from t where exists
(select 1 from
(select is_rp,min(lv) lv from t where is_rp=1 group by is_rp) a
where a.is_rp=t.is_rp and a.lv=t.lv)
你运行一下看看结果