Oracle层次查询技巧
create table emp_hire as
selectempno,mgr,ename from emp
select * from emp_hire
EMPNO
MGR
ENAME
5555
7900
ggg
7369
7902
SMITH
7499
7698
ALLEN
7521
7698
WARD
7566
7839
JONES
7654
7698
MA & RTIN
7698
7839
BLAKE
7782
7839
CLARK
7788
7566
SCOTT
7839
KING
7844
7698
TURNER
7900
7698
JAMES
7902
7566
FORD
7934
7782
MILLER
7876
7788
ADAMS
这些数据存在着层次关系
select
lpad('*',level,'*')||e.ename ename,
e.empno,
mgr fa_id,
(select ename from emp_hire where empno=e.mgr) fa_name
from emp_hiree
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ENAME
EMPNO
FA_ID
FA_NAME
*KING
7839
**JONES
7566
7839
KING
***SCOTT
7788
7566
JONES
****ADAMS
7876
7788
SCOTT
***FORD
7902
7566
JONES
****SMITH
7369
7902
FORD
**BLAKE
7698
7839
KING
***ALLEN
7499
7698
BLAKE
***WARD
7521
7698
BLAKE
***MA & RTIN
7654
7698
BLAKE
***TURNER
7844
7698
BLAKE
***JAMES
7900
7698
BLAKE
****ggg
5555
7900
JAMES
**CLARK
7782
7839
KING
***MILLER
7934
7782
CLARK
其中
start with -- this identifies all LEVEL=1 nodes in the tree
connect by -- describes how to walk from the parent nodes above to their children and their childrens children.
层次是一棵树, 又如同一个家族图谱
每一个子节电只有一个父节点;
每一个分支,最末端是叶节点;
SELECT
ename Employee,
CONNECT_BY_ISLEAF IsLeaf,
LEVEL lev,
SYS_CONNECT_BY_PATH(ename, '/') Path
FROM emp
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
EMPLOYEE
ISLEAF
LEV
PATH
KING
0
1
/KING
JONES
0
2
/KING/JONES
SCOTT
0
3
/KING/JONES/SCOTT
ADAMS
1
4
/KING/JONES/SCOTT/ADAMS
FORD
0
3
/KING/JONES/FORD
SMITH
1
4
/KING/JONES/FORD/SMITH
BLAKE
0
2
/KING/BLAKE
ALLEN
1
3
/KING/BLAKE/ALLEN
WARD
1
3
/KING/BLAKE/WARD
MA & RTIN
1
3
/KING/BLAKE/MA & RTIN
TURNER
1
3
/KING/BLAKE/TURNER
JAMES
0
3
/KING/BLAKE/JAMES
ggg
1
4
/KING/BLAKE/JAMES/ggg
CLARK
0
2
/KING/CLARK
MILLER
1
3
/KING/CLARK/MILLER
检查一个层次是否存在闭循环
CONNECT_BY_ISCYCLE
找出根节点
CONNECT_BY_ROOT
SELECT
ename Employee,
CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT ename
FROM emp
START WITH MGR IS NULL
CONNECT BY NOCYCLE PRIOR EMPNO = MGR
EMPLOYEE
CONNECT_BY_ISCYCLE
CONNECT_BY_ROOTENAME
KING
0
KING
JONES
0
KING
SCOTT
0
KING
ADAMS
0
KING
FORD
0
KING
SMITH
0
KING
BLAKE
0
KING
ALLEN
0
KING
WARD
0
KING
MA & RTIN
0
KING
TURNER
0
KING
JAMES
0
KING
ggg
0
KING
CLARK
0
KING
MILLER
0
KING
层次查询的过滤条件
SELECT
ename Employee,
CONNECT_BY_ISLEAF IsLeaf,
LEVEL lev,
SYS_CONNECT_BY_PATH(ename, '/') Path
FROM emp
where level