ORACLE--Connect By、Level、Start With的使用(Hierarchical query-层次查询)

查找员工编号为7369的领导:

1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO  START WITH E.EMPNO = 7876
2 ORDER BY LEVEL DESC

"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.

Easiest to use an example on emp. If we start with "where mgr is NULL", we generate the 
set of employees that have no mgr (they are the top of the tree). If we

CONNECT BY PRIOR EMPNO = /* current */ MGR

that will take all of the PRIOR records (the start with at first) and find all records 
such that the MGR column equals their EMPNO (find all the records of people managed by 
the people we started with).

使用WITH语句优化查询结果:优化等级

 1 WITH A AS
 2  (SELECT MAX(LEVEL) + 1 LVL
 3     FROM EMP E
 4   CONNECT BY PRIOR E.MGR = E.EMPNO
 5    START WITH E.EMPNO = 7876
 6    ORDER BY LEVEL DESC)
 7 SELECT A.LVL 最高等级加1,
 8        LEVEL 当前等级,
 9        A.LVL - LEVEL 优化后等级,
10        E.* 　FROM A,
11        EMP E CONNECT BY PRIOR E.MGR = E.EMPNO START WITH E.EMPNO = 7876 ORDER BY LEVEL DESC

查找员工编号为7839的所有下属(7839为king):

1 SELECT LEVEL 等级, E.*
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.EMPNO = 7839

--构造整个的层次结构