ORACLE TREE

--分層查詢
/**
start with 字段=？；
prior
connect by

level:所在樹的等級（1為根節點）
connect_by_isleaf：所在節點是否存在下面其他節點（存在為0，不存在為1，即葉子節點）

sys_connect_by_path:拼接

siblings：排序

**/

--樹形查詢
create table myemp 
(eid number(6) primary key,
 ename varchar2(20) not null,
 epriorid number(6))
 tablespace users;
 
 --SELECT * FROM myemp;
 --DELETE  FROM  myemp;
 INSERT INTO myemp VALUES(1,'iris',0);
 INSERT INTO myemp VALUES(2,'jones',1);
 INSERT INTO myemp VALUES(3,'susan',1);
 INSERT INTO myemp VALUES(4,'jane',2);
 INSERT INTO myemp VALUES(5,'hins',2);
 INSERT INTO myemp VALUES(6,'hugn',2);
 INSERT INTO myemp VALUES(7,'heng',3);
 INSERT INTO myemp VALUES(8,'freesa',3);
 INSERT INTO myemp VALUES(9,'aiping',4);
 INSERT INTO myemp VALUES(10,'jane',5); 

--从上往下抓
 select eid,ename,
 epriorid,--值越小越接近根节点
 (prior ename)主管名稱,
 level 等級一為根節點,
 connect_by_isleaf 一為葉子節點，
 sys_connect_by_path(ename,',') as names 
 from myemp
 start with eid=1
 connect by (prior eid)=epriorid
 order siblings by eid;--排序同時保持樹態
 
 
--从下往上抓
 select eid,ename,
 epriorid,--值越小越接近根节点
 (prior ename)主管名稱,
 level 等級一為根節點,
 connect_by_isleaf 一為葉子節點，
 sys_connect_by_path(ename,',') as names 
 from myemp
 start with eid=5
 connect BY  eid=(PRIOR epriorid)
 order siblings by eid;--排序同時保持樹態