Oracle中connect by 的执行结果记载

1.

select id,yy,regexp_count(nvl(yy,' '),',')+1 a1 from (
select 1 id,'01,06,07,09' yy from dual union all
select 2 id,'02' from dual union all
select 3 id,null from dual union all
select 4 id,' ' from dual union all
select 5 id,'06,07' from dual
) connect by level<=( regexp_count(nvl(yy,' '),',')+1 ) and prior id=id and prior dbms_random.value is not null;

 

2.select id,sname,sys_connect_by_path(sname,'->') a1,sys_connect_by_path(id,',') a2 from cs_thz_1 connect by level<=2 and prior id=id and prior dbms_random.value is not null  ;

 

3.select id,sname,sys_connect_by_path(sname,'->') a1,sys_connect_by_path(id,',') a2 from cs_thz_1 start with id in (1,2) connect by prior id=parentid ;  

 

/*建表语句
drop table cs_thz_1;
create table cs_thz_1(
id number(10)
,sname varchar2(100)
,sremark varchar2(1000)
,parentid number(10)
)
;
truncate table cs_thz_1;
insert into cs_thz_1(id,sname,sremark,parentid)
select 1 id,'中国' sname,' ' sremark,null parentid from dual union all
select 2 id,'美国' sname,' ' sremark,null parentid from dual union all
select 3 id,'北京' sname,' ' sremark,1 parentid from dual union all
select 4 id,'广东' sname,' ' sremark,1 parentid from dual union all
select 5 id,'广州' sname,' ' sremark,4 parentid from dual
;
commit;
*/

转载于:https://www.cnblogs.com/jiangqingfeng/p/10978773.html