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 pidorder  by id asc ;

 

     4.查詢全部節點關係

select * from A-- where 語句位置start with  pid is null connect by prior  id =  pidorder  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);