ORACLE connect by prior

• 原理

http://xxciof.blog.163.com/blog/static/7978132720095193113752/

• 测试

1. 数据

   

2. 数据结构

1

2 3

4

     3. 查询根节点

select * from A

-- where 语句位置
start with  pid is null
 connect by   id = prior pid
order  by id asc ;

 

     4.查询全部节点关系

select * from A

-- where 语句位置
start with  pid is null
 connect by prior  id =  pid
order  by id asc ;

 

 

 

===========================建表资料============================================

CREATE TABLE A (

   name  varchar(5)  NOT NULL  ,
   id  int PRIMARY KEY ,
   pid   int  ,
   foreign key(pid)  references  A (id)
) ;

INSERT INTO A (NAME,ID )VALUES( 'a1',1 );
INSERT INTO A VALUES( 'a2',2,1);
INSERT INTO A VALUES( 'a3',3,1);
INSERT INTO A VALUES( 'a4',4,2);