ORACLE分層查詢start with和connect by怎么用

這篇文章主要為大家展示了“ORACLE分層查詢start with和connect by怎么用”，內容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領大家一起研究并學習一下“ORACLE分層查詢start with和connect by怎么用”這篇文章吧。

分層查詢是select 語句的擴展，目的是迅速找出表中列-列的隸屬關系。

19.1 樹的遍歷

ORACLE是一個關系數據庫管理系統,它用表的形式組織數據,在某些表中的數據還呈現出樹型結構的聯系。例如，我們現在討論雇員信息表EMP，其中含有雇員編號（EMPNO）和經理（MGR）兩列，通過這兩列反映出來的就是雇員之間領導和被領導的關系。他們之間的這種關系就是一種樹結構。

樹的遍歷有兩個方向

top--down 自上而下

即父親找兒子，一個父親可能有幾個兒子，一個兒子可能有幾個孫子，遍歷不能丟了兒子，順序以左為先。

down--top 自底向上

即兒子找父親，一個兒子只能有一個父親，所以順序應該是：孫子->兒子-->父親-->爺爺。

19.2 CONNECT BY 和 START WITH

在SELECT命令中使用CONNECT BY 和 START WITH 子句可以查詢表中的樹型結構關系。其命令格式如下：

SELECT ...

CONNECT BY {PRIOR 列名1=列名2|列名1=PRIOR 列名2}

[START WITH]；

19.3 關于CONNECT BY子句

理解CONNECT BY PRIOR 子句至關重要，它確定了樹的檢索方向: 是top --> down（父-->子）還是down --> top（子-->父）。

在分層表中，表的父列與子列是確定的（身份固定），如：在emp表中empno是子列（下級）， mgr是父列（上級）。

RIOR關鍵字就像一個箭頭("-->")，

connect by prior empno = mgr

connect by mgr = prior empno

兩句語法等同，都是說mgr（父）--> empno(子），因此樹的檢索方向是top --> down。

connect by empno = prior mgr

connect by prior mgr = empno

兩句語法等同，都是說empno(子）--> mgr(父），因此樹的檢索方向是down --> top。

19.4 START WITH 子句為可選項，用來標識哪個節點作為查找樹型結構的根節點。若該子句被省略，則表示所有滿足查詢條件的行作為根節點（每一行都會成為一個樹根）。

例1 以樹結構方式顯示EMP表的數據。

SQL>select empno,ename,mgr from emp connect by prior empno=mgr start with empno=7839;

仔細看empno這一列輸出的順序，就是上圖樹狀結構每一條分支（從根節點開始）的結構。

mgr（父）--> empno(子），因此樹的檢索方向是top --> down。

例2 從SMITH節點開始自底向上查找EMP的樹結構。

SQL>select empno,ename,mgr

from emp

connect by empno=prior mgr

start with empno=7369

/

在這種自底向上的查找過程中，只有樹中的一枝被顯示。

empno(子）--> mgr(父），因此樹的檢索方向是down --> top

19.5  定義查找起始節點

在自頂向下查詢樹結構時，不但可以從根節點開始，還可以定義任何節點為起始節點，以此開始向下查找。這樣查找的結果就是以該節點為開始的結構樹的一枝。

例3 查找7566(JONES)直接或間接領導的所有雇員信息。

SQL＞SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH EMPNO=7566

/

START WITH 不但可以指定一個根節點，還可以指定多個根節點。

例4 查找由FORD和BLAKE 領導的所有雇員的信息。

SQL>SELECT EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME IN ('FORD','BLAKE')

/      父親找兒子

19.6 使用LEVEL

在查詢中，可以使用偽列LEVEL顯示每行數據的有關層次。LEVEL將返回樹型結構中當前節點的層次。

偽列LEVEL為數值型，可以在SELECT 命令中用于各種計算。

例5 使用LEVEL改變查詢結果的顯示形式。

SQL> COLUMN LEVEL FORMAT A20

SQL> SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME='KING'

/

在SELECT使用了函數LPAD，該函數表示以LEVEL*3個空格進行填充，由于不同行處于不同的節點位置，具有不同的LEVEL值，因此填充的空格數將根據各自的層號確定，空格再與層號拼接，結果顯示出這種層次關系。

只查看第2層的員工信息：

SQL> select t1.* from (select level LNUM ,ename,mgr from emp connect by prior empno=mgr start with ename='KING') t1 where LNUM=2;

19.7 節點和分支的裁剪

在對樹結構進行查詢時，可以去掉表中的某些行，也可以剪掉樹中的一個分支，使用WHERE子句來限定樹型結構中的單個節點，以去掉樹中的單個節點，但它卻不影響其后代節點（自頂向下檢索時）或前輩節點（自底向頂檢索時）。

SQL>SELECT LPAD(LEVEL,LEVEL*3,' ')

as "LEVEL",EMPNO,ENAME,MGR

FROM EMP

WHERE ENAME<>'SCOTT'

CONNECT BY PRIOR EMPNO=MGR

START WITH ENAME='KING'

/

在這個查詢中，僅剪去了樹中單個節點SCOTT。若希望剪去樹結構中的某個分支，則要用CONNECT BY 子句。CONNECT BY 子句是限定樹型結構中的整個分支，既要剪除分支上的單個節點，也要剪除其后代節點（自頂向下檢索時）或前輩節點（自底向頂檢索時）。

例8．顯示KING領導下的全體雇員信息，除去SCOTT領導的一支。

SQL>SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!='SCOTT' START WITH ENAME='KING';

/

                           












這個查詢結果就除了剪去單個節點SCOTT外，還將SCOTT的子節點ADAMS剪掉，即把SCOTT這個分支剪掉了。

當然WHERE子句可以和CONNECT BY子句聯合使用，這樣能夠同時剪掉單個節點和樹中的某個分支。

例9．顯示KING領導全體雇員信息，除去雇員SCOTT，以及BLAKE領導的一支。

SCOTT@hyyk> SELECT LPAD(LEVEL,LEVEL*3,' ') as "LEVEL",EMPNO,ENAME,MGR FROM EMP CONNECT BY PRIOR EMPNO=MGR AND ENAME!='SCOTT' and ENAME!='BLAKE' START WITH ENAME='KING';

在使用SELECT 語句來報告樹結構報表時應當注意，CONNECT BY子句不能作用于出現在WHERE子句中的表連接。如果需要進行連接，可以先用樹結構建立一個視圖，再將這個視圖與其他表連接，以完成所需要的查詢。

以上是“ORACLE分層查詢start with和connect by怎么用”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！