【Oracle】树状结构查询



【Oracle】树状结构查询

 

[sql] 

--oracle中的树状结构查询实际上就是一个中序遍历  

--显示以KING为根节点的树，start with定义起始节点  

SELECT *  

FROM   emp a  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

--置于等号前面，由父节点向子节点方向检索；置于等号后面，则从子节点向父节点方向检索    

SELECT *  

FROM   emp a  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

--使用伪列level来显示层级关系  

SELECT *  

FROM   emp a  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

  

SELECT level, a.*  

FROM   emp a  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

--使用函数 sys_connect_by_path 显示全路径  

SELECT LEVEL, sys_connect_by_path(a.ename, '/'), a.*  

FROM   emp a  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

--where子句只限定单个节点，不影响子其子节点或者父节点  

SELECT LEVEL, sys_connect_by_path(a.ename, '/'), a.*  

FROM   emp a  

WHERE a.empno <> 7369  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

--connect by 子句限定整个以当前节点为起始节点的整个分支  

--connect_by_root 可以在列之前显示最高节点的内容  

SELECT LEVEL, connect_by_root(a.job),sys_connect_by_path(a.ename, '/'), a.*  

FROM   emp a  

WHERE a.empno <> 7369  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

--connect_by_isleaf 显示当前行是否叶子节点，1：是；0：否  

SELECT LEVEL, connect_by_isleaf,sys_connect_by_path(a.ename, '/'), a.*  

FROM   emp a  

WHERE a.empno <> 7369  

START  WITH a.empno = 7839  

CONNECT BY PRIOR a.empno = a.mgr;  

--connect_by_iscycle 10g中开始有的新特性，用来判断当前节点是否产生了循环，0：否；1：是  

SELECT LEVEL, connect_by_iscycle, connect_by_isleaf,sys_connect_by_path(a.ename, '/'), a.*  

FROM   emp a  

WHERE a.empno <> 7369  

START  WITH a.empno = 7839  

CONNECT BY NOCYCLE PRIOR a.empno = a.mgr;