Oracle層次化查詢學習總結，oracle層次

Oracle層次化查詢學習總結，oracle層次
層次化查詢 文檔目錄：

1.1 概要 21.2 節點術語 21.3 使用CONNECT BY 和 START WITH子句31.4 層次查詢執行個體   31.5 從下向上查詢   41.6 從層次查詢中刪除節點和分支5

文檔地址：

http://wenku.baidu.com/view/324fb47a700abb68a982fbd1

本文

備忘：圖片無法顯示，請到文庫查看

本文地址：http://blog.csdn.net/sunansheng/article/details/46492299

1.       層次化查詢1.1 概要

        我們經常可以見到組織為層次的資料，比如一個公司的工作的人員就構成了一個層次，這個層次可以使用樹的形式來表示。例如Oracle的表scott.emp，就可以使用如下的樹來表示：

下面是查詢scott.emp表返回的行：

        其中MGR列是一個自引用列，它指回EMPNO列。MGR表示一個員工的管理者或者上級。在ORACLE中可以使用CONNECT BY 和 START WITH子句來查詢這種階層的資料。

1.2 節點術語

        如所示，這些元素構成了一棵樹。有關節點構成樹有一些技術術語，如下：

1)       根節點：根節點是位於樹頂端的節點。如所示，根節點是“KING”。

2)       父節點：父節點的下面有一個或多個節點。

3)       子節點：子節點上面有一個父節點。

4)       分葉節點：分葉節點是沒有子節點的節點。

 

1.3 使用CONNECT BY 和 START WITH子句

SELECT語句的CONNECT BY 和 START WITH  子句的文法如下：

SELECT [LEVEL],column... FROM TABLE [WHERE where_condition] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]]

其中：

l  LEVEL：是一個”偽列”,代表樹的第幾層。對於根節點來說，LEVEL為1，根節點的子節點層次為2…

l  start_condition ：定義了層次化查詢的起點。層次化查詢必須指定 START WITH 子句。例如，可以指定 start_condition 定義為 EMPNO=7839 (KING)，代表從KING 開始查詢。

l  prior_condition：定義了父行與子行的關係。當編寫層次化查詢時必須定義CONNECT BY PRIOR子句。例如，可以將prior_condition定義為ENPNO=MGR，表示父節點的 ENPNO 和子節點的MGR 之間存在關係，也就是說，子節點的 MGR 指向父節點的 ENPNO 。其中跟在PRIOR 關鍵字後面的代表父節點。

1.4 層次查詢執行個體

1)       查詢EMP表的層次關係。查詢“JONES “下面的員工

SELECT e.empno,        e.mgr,        e.ename   FROM scott.emp e  START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr   查詢結果： EMPNO   MGR ENAME ----- ----- ----------  7566  7839 JONES  7788  7566 SCOTT  7876  7788 ADAMS  7902  7566 FORD  7369  7902 SMITH

預期結果：

                  

2)       使用偽列 LEVEL

SELECT LEVEL,        e.empno,        e.mgr,        e.ename   FROM scott.emp e  START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr   查詢結果：      LEVEL EMPNO   MGR ENAME ---------- ----- ----- ----------          1  7566  7839 JONES          2  7788  7566 SCOTT          3  7876  7788 ADAMS          2  7902  7566 FORD          3  7369  7902 SMITH

 

1.5 從下向上查詢

        不一定非要按照從父節點到子節點的順序從上至下遍曆樹；也可以從某個子節點開始，從下而上遍曆。實現的方法是交換父節點和子節點在CONNECT BY PRIOR子句中的順序。例如，CONNECT BY PRIOR mgr = empno 可以將子節點的 mgr 串連到父節點的 empno 上。

--從分葉節點”SMITH”,從下向上查詢到根節點： SELECT LEVEL,        e.empno,        e.mgr,        e.ename   FROM scott.emp e  START WITH e.ename = 'SMITH' CONNECT BY PRIOR e.mgr = e.empno;   查詢結果：      LEVEL EMPNO   MGR ENAME ---------- ----- ----- ----------          1  7369  7902 SMITH          2  7902  7566 FORD          3  7566  7839 JONES          4  7839       KING

 

1.6 從層次查詢中刪除節點和分支

1.       刪除節點

可以用WHERE子句從查詢樹中除去某個特定的節點，下面這個查詢使用WHERE e.ename != 'FORD'子句從結果中除去FORD

SELECT LEVEL,        e.empno,        e.mgr,        e.ename   FROM scott.emp e  WHERE e.ename != 'SCOTT'  START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr;   查詢結果：      LEVEL EMPNO   MGR ENAME ---------- ----- ----- ----------          1  7566  7839 JONES          3  7876  7788 ADAMS          2  7902  7566 FORD          3  7369  7902 SMITH

  可以看到，儘管  SCOTT 已經從結果中除去了，但是他的下屬 ADAMS 仍然在結果中。為了將整個分支都從查詢結果中除去，可以再CONNECT BY PRIOR子句中使用AND 子句。例如下面這個例子使用AND  e.ename != 'SCOTT' 將SCOTT及其所有下屬從結果中除去

2.       刪除分支

SELECT LEVEL,        e.empno,        e.mgr,        e.ename   FROM scott.emp e  START WITH e.ename = 'JONES' CONNECT BY PRIOR e.empno = e.mgr        AND e.ename != 'SCOTT';   查詢結果：      LEVEL EMPNO   MGR ENAME ---------- ----- ----- ----------          1  7566  7839 JONES          2  7902  7566 FORD          3  7369  7902 SMITH