oracle中connect by prior父子查询

--父子查询

--prior 代表 上一条记录

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 union all

select 6 id,'珠海' sname,' ' sremark,4 parentid from dual union all

select 7 id,'白云区' sname,' ' sremark,5 parentid from dual union all

select 8 id,'天河区' sname,' ' sremark,5 parentid from dual union all

select 9 id,'珠江新城' sname,' ' sremark,8 parentid from dual union all

select 10 id,'纽约' sname,' ' sremark,2 parentid from dual ;

commit;

--查询广州的下级区域(广州第一行，且广州是最大)  ,prior就代表第一行要显示的,第一行和第二行相比,大的那行用id,小的那行用parentid, 也可以说prior代表上一行

select t.*,level  from cs_thz_1 t start with id=5 connect by parentid= prior id ;

 

--查询广州的上级区域(广州第一行，且广州最小)  他作为儿子(prior的另一边是他)

select a.*,level from cs_thz_1 a start with id=5 connect by prior parentid = id  ;

--sys_connect_by_path 顶级往下翻 select id,sname,parentid,sys_connect_by_path(sname,'-'),level 自生成lvl from cs_thz_1 start with parentid is null connect by parentid =prior id  ;

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