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 = 78762 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 E3 CONNECT BY PRIOR E.EMPNO = E.MGR4  START WITH E.EMPNO = 7839

--構造整個的階層

1 select lpad(' ',level*2,' ')||ename ename, empno, mgr2     from emp3     START WITH MGR IS NULL4     CONNECT BY PRIOR EMPNO = MGR

So, KING is the start with set then JONES BLAKE and CLARK fall under him. Each of them 
becomes the PRIOR record in turn and their trees are expanded.

使用Connect By 結合 level構造虛設項目列:

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

使用rownum實作類別似的功能:

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

---------------------待續-----------------------

使用UNION ALL構造兩層節點的樹:

視圖如下所示:

 1 CREATE OR REPLACE VIEW TREE_VIEW AS 2 SELECT 3  '1' AS rootnodeid, 4  'xxxx有限責任公司' AS treename, 5  '-1'  AS parent_id 6 FROM dual 7 UNION 8 SELECT 9   to_char(d.deptno),10   d.dname || '_' ||d.loc,11   '1' AS parent_id12  FROM dept d;

查詢語句:

1 SELECT T.*, LEVEL2   FROM TREE_VIEW T3  START WITH T.PARENT_ID = '-1'4 CONNECT BY PRIOR T.ROOTNODEID = T.PARENT_ID

-----以下為更新內容:

1、先查看總共有幾個等級：

1 SELECT COUNT(LEVEL)2   FROM EMP E3 CONNECT BY PRIOR E.EMPNO = E.MGR4  START WITH E.MGR IS NULL;

2、查看每個等級的人數。主要是通過LEVEL進行GROUP BY

1 SELECT COUNT(LEVEL)2   FROM EMP E3 CONNECT BY PRIOR E.EMPNO = E.MGR4  START WITH E.MGR IS NULL5  GROUP BY LEVEL;

3、Oracle 10g提供了一個簡單的connect_by_isleaf=1,

0 表示非葉子節點

1 SELECT LEVEL AS 等級, CONNECT_BY_ISLEAF AS 是否是葉子節點, E.*2   FROM EMP E3 CONNECT BY PRIOR E.EMPNO = E.MGR4  START WITH E.MGR IS NULL

4、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column 是字元型或能自動轉

換成字元型的列名。它的主要目的就是將父節點到當前節點的”path”按照指定的模式展現出現。這個函數只能使用在層次查詢中。

1 SELECT LEVEL AS 等級,2        CONNECT_BY_ISLEAF AS 是否是葉子節點,3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>')4   FROM EMP E5 CONNECT BY PRIOR E.EMPNO = E.MGR6  START WITH E.MGR IS NULL;

5、修剪樹枝和節點: