Oracle的CONNECT BY LOOP我自己使用的数据。关于where 跟join.

create table test1(superid varchar2(20),id varchar2(20));

insert into test1 values('0','1');
insert into test1 values('0','2');

insert into test1 values('1','11');
insert into test1 values('1','12');

insert into test1 values('2','21');
insert into test1 values('2','22');

insert into test1 values('11','111');
insert into test1 values('11','112');

insert into test1 values('12','121');
insert into test1 values('12','122');

insert into test1 values('21','211');
insert into test1 values('21','212');

insert into test1 values('22','221');
insert into test1 values('22','222');

commit;

select * from test1

select level||' layer',lpad(' ',level*5)||id id ,connect_by_isleaf
from test1
start with superid = '0' connect by prior id=superid;

select level||' layer',lpad(' ',level*5)||id id ,connect_by_isleaf,substr( sys_connect_by_path(id,'>'),2)
from test1 
where connect_by_isleaf=1
start with superid = '0' connect by prior id=superid;

 

--------------Join -------------

select level||' layer',lpad(' ',level*5)||id id ,connect_by_isleaf,substr( sys_connect_by_path(id,'>'),2)
from test1 ,(select 111 col from dual) tmp
where tmp.col=to_number(test1.id)
start with superid = '0' connect by prior id=superid;

--------------Where -------------

select level||' layer',lpad(' ',level*5)||id id ,connect_by_isleaf,substr( sys_connect_by_path(id,'>'),2)
from test1 
where 111=to_number(test1.id)
start with superid = '0' connect by prior id=superid;