oracle+connect+by+level,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;

*/

标签：sname,union,记载,connect,dual,Oracle,id,select

来源： https://www.cnblogs.com/jiangqingfeng/p/10978773.html