drop table test2;
create table test2 (id varchar2(20) ,name varchar(10),parent_id varchar2(20) );
insert into test2 values('1','a','0');
insert into test2 values('2','b','7');
insert into test2 values('3','c','2');
insert into test2 values('4','d','0');
insert into test2 values('6','e','3');
insert into test2 values('7','f','1');
insert into test2 values('8','g','9');
insert into test2 values('9','h','4');
SELECT id ,name ,parent_id, level
FROM test2
start with parent_id ='7'
connect by prior id=parent_id
SELECT id ,name ,parent_id
FROM test2
start with parent_id ='7'
connect by instr( parent_id,prior id||',')=1
or instr( parent_id,prior ','||id)>0 --这里有问题
or instr(parent_id,prior ','||id||',')>0
or prior id= parent_id;
SELECT id ,name ,parent_id
FROM test2
start with parent_id ='7'
connect by instr( parent_id,prior id||',')=1 --第一个
or instr( parent_id,','||prior id)=( length(parent_id)-length(prior id) )--末尾 这里有问题
or instr(parent_id, ','||prior id||',')>0 --中间
or prior id= parent_id; --父节点就一个
高手帮我改下,末尾的情况