--======================================================

--SQL 基礎 --> 層次化查詢 (START BY ... CONNECT BY PRIOR)

--======================================================

?

??? 層次化查詢 , 即樹型結構查詢 , 是 SQL 中經常用到的功能之一 , 通常由根節點 , 父節點 , 子節點 , 葉節點組成 , 其語法如下 :

?????? SELECT [LEVEL] , column , expression ,...

?????? FROM table_name

?????? [WHERE where_clause]

?????? [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

??????

?????? LEVEL: 為偽列 , 用于表示樹的層次

?????? start_condition ：層次化查詢的起始條件

?????? prior_condition ：定義父節點和子節點之間的關系

???

??? -- 使用 start with ...connect by prior 從根節點開始遍歷

??? SQL > select empno , mgr , ename , job from emp

??? ? 2 ? start with empno = 7839

??? ? 3 ? connect by prior empno = mgr ;

?

?????? ? EMPNO ??????? MGR ENAME ????? JOB

??? ---------- ---------- ---------- ---------

?????? ? 7839 ??????????? KING ?????? PRESIDENT

?????? ? 7566 ?????? 7839 JONES ????? MANAGER

?????? ? 7788 ?????? 7566 SCOTT ????? ANALYST

?????? ? 7876 ?????? 7788 ADAMS ????? CLERK

?????? ? 7902 ?????? 7566 FORD ?????? ANALYST

?????? ? 7369 ?????? 7902 SMITH ????? CLERK

?????? ? 7698 ?????? 7839 BLAKE ????? MANAGER

?????? ? 7499 ?????? 7698 ALLEN ????? SALESMAN

?????? ? 7521 ?????? 7698 WARD ?????? SALESMAN

?????? ? 7654 ?????? 7698 MARTIN ???? SALESMAN

?????? ? 7844 ?????? 7698 TURNER ???? SALESMAN

?

?????? ? EMPNO ??????? MGR ENAME ????? JOB

??? ---------- ---------- ---------- ---------

?????? ? 7900 ?????? 7698 JAMES ????? CLERK

?????? ? 7782 ?????? 7839 CLARK ????? MANAGER

?????? ? 7934 ?????? 7782 MILLER ???? CLERK

?

??? 14 rows selected .

???

??? 樹型結構遍歷過程 ( 通過上面的查詢來描述 )

?????? 1 ). 從根節點開始 ( 即 where_clause 中的條件 , 如果為非根節點則分根節點作為根節點開始遍歷 , 如上例 empno = 7839 )

?????? 2 ). 遍歷根節點 ( 得到 empno = 7839 記錄的相關信息 )

?????? 3 ). 判斷該節點是否存在由子節點，如果則訪問最左側未被訪問的子節點 , 轉到 ), 否則下一步

?????? ??? 如上例中 prior_condition 為 empno = mgr , 即子節點的 mgr 等于父節點的 empno , 在此時 mgr 為 7839 的記錄

?????? 4 ). 當節點為葉節點，則訪問完畢 , 否則 , 轉到 )

?????? 5 ). 返回到該節點的父節點 , 轉到 )

??????

??? -- 偽列 level 的使用

? ?? -- 注意 connect by prior empno = mgr 的理解

??? --prior 表示前一條記錄 , 即下一條返回記錄的 mgr 應當等于前一條記錄的 empno

?

??? SQL > select level , empno , mgr , ename , job from emp

??? ? 2 ? start with ename = 'KING'

??? ? 3 ? connect by prior empno = mgr

??? ? 4 ? order by level ;

?

?????? ? LEVEL ????? EMPNO ??????? MGR ENAME ????? JOB

??? ---------- ---------- ---------- ---------- ---------

?????????? ? 1 ?????? 7839 ??????????? KING ?????? PRESIDENT

?????????? ? 2 ?????? 7566 ?????? 7839 JONES ????? MANAGER

?????????? ? 2 ?????? 7698 ?????? 7839 BLAKE ????? MANAGER

?????????? ? 2 ?????? 7782 ?????? 7839 CLARK ????? MANAGER

?????????? ? 3 ?????? 7902 ?????? 7566 FORD ?????? ANALYST

?????????? ? 3 ?????? 7521 ?????? 7698 WARD ?????? SALESMAN

?????????? ? 3 ?????? 7900 ?????? 7698 JAMES ????? CLERK

?????????? ? 3 ?????? 7934 ?????? 7782 MILLER ???? CLERK

?????????? ? 3 ?????? 7499 ?????? 7698 ALLEN ????? SALESMAN

?????????? ? 3 ?????? 7788 ?????? 7566 SCOTT ????? ANALYST

?????????? ? 3 ?????? 7654 ?????? 7698 MARTIN ???? SALESMAN

?

?????? ? LEVEL ????? EMPNO ??????? MGR ENAME ????? JOB

??? ---------- ---------- ---------- ---------- ---------

?????????? ? 3 ?????? 7844 ?????? 7698 TURNER ???? SALESMAN

?????????? ? 4 ?????? 7876 ?????? 7788 ADAMS ????? CLERK

?????????? ? 4 ?????? 7369 ?????? 7902 SMITH ????? CLERK

???

??? -- 獲得層次數

??? SQL > select count ( distinct level ) "Level" from emp

??? ? 2 ? start with ename = 'KING'

??? ? 3 ? connect by prior empno = mgr ;

?

?????? ? Level

??? ----------

?????????? ? 4 ?

?????????? ?

??? -- 格式化層次查詢結果 ( 使用左填充 * level - 1 個空格 )

??? SQL > col Ename for a30

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename" ,

??? ? 3 ??? job

??? ? 4 ? from emp

??? ? 5 ? start with ename = 'KING'

??? ? 6 ? connect by prior empno = mgr ;

?

?????? ? LEVEL Ename ????????????????????????? JOB

??? ---------- ------------------------------ ---------

?????????? ? 1 ? KING ????????????????????????? PRESIDENT

?????????? ? 2 ??? JONES ?????????????????????? MANAGER

?????????? ? 3 ????? SCOTT ???????????????????? ANALYST

?????????? ? 4 ??????? ADAMS ?????????????????? CLERK

?????????? ? 3 ????? FORD ????????????????????? ANALYST

?????????? ? 4 ??????? SMITH ?????????????????? CLERK

?????????? ? 2 ??? BLAKE ?????????????????????? MANAGER

?????????? ? 3 ????? ALLEN ??????????? ????????? SALESMAN

?????????? ? 3 ????? WARD ????????????????????? SALESMAN

?????????? ? 3 ????? MARTIN ??????????????????? SALESMAN

?????????? ? 3 ????? TURNER ??????????????????? SALESMAN

?

?????? ? LEVEL Ename ????????????????????????? JOB

??? ---------- ------------------------------ ---------

?????? ??? ? 3 ????? JAMES ???????????????????? CLERK

?????????? ? 2 ??? CLARK ?????????????????????? MANAGER

?????????? ? 3 ????? MILLER ??????????????????? CLERK

?

??? 14 rows selected .

???

??? -- 從非根節點開始遍歷 ( 只需修改 start with 中的條件即可 )

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename" ,

??? ? 3 ??? job

??? ? 4 ? from emp

??? ? 5 ? start with ename = 'SCOTT'

??? ? 6 ? connect by prior empno = mgr ;

?

?????? ? LEVEL Ename ????????????????????????? JOB

??? ---------- ------------------------------ ---------

?????????? ? 1 ? SCOTT ???????????????????????? ANALYST

?????????? ? 2 ? ?? ADAMS ?????????????????????? CLERK

?

??? -- 從下向上遍歷 ( 交換 connect by prior 中的條件即可 , 使用 mgr = empno)

??? --注意connect by prior mgr = empno 的理解
??? --prior表示前一條記錄,即下一條返回記錄的empno應當等于前一條記錄的mgr

?

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename" ,

??? ? 3 ??? job

??? ? 4 ? from emp

??? ? 5 ? start with ename = 'SCOTT'

??? ? 6 ? connect by prior mgr = empno ;

?

?????? ? LEVEL Ename ????????????????????????? JOB

??? ---------- ------------------------------ ---------

?????????? ? 1 ? SCOTT ???????????????????????? ANALYST

?????????? ? 2 ??? JONES ?????????????????????? MANAGER

?????????? ? 3 ????? KING ????????????????????? PRESIDENT

?????????? ?

??? -- 從下向上遍歷 ( 也可以將 prior 置于等號右邊 , 得到相同的結果 )

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename" ,

??? ? 3 ??? job

??? ? 4 ? from emp

??? ? 5 ? start with ename = 'SCOTT'

??? ? 6 ? connect by empno = prior mgr ;

?

?????? ? LEVEL Ename ????????????????????????? JOB

??? ---------- ------------------------------ ---------

?????????? ? 1 ? SCOTT ???????????????????????? ANALYST

?????????? ? 2 ??? JONES ?????????????????????? MANAGER

?????????? ? 3 ????? KING ????????????????????? PRESIDENT

?????????? ?

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

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename"

??? ? 3 ??? , job

??? ? 4 ? from emp

??? ? 5 ? where ename != 'SCOTT' ??? -- 通過 where 子句來過濾 SCOTT 用戶，但 SCOTT 的下屬 ADAMS 并沒有過濾掉

??? ? 6 ? start with empno = 7839 ???

??? ? 7 ? connect by prior empno = mgr ;

?

?????? ? LEVEL Ename ??????????????? JOB

??? ---------- -------------------- ---------

?????????? ? 1 ? KING ??????????????? PRESIDENT

?????????? ? 2 ??? JONES ???????????? MANAGER

?????????? ? 4 ??????? ADAMS ???????? CLERK

?????????? ? 3 ????? FORD ??????????? ANALYST

?????????? ? 4 ??????? SMITH ???????? CLERK

?????????? ? 2 ??? BLAKE ???????????? MANAGER

?????????? ? 3 ????? ALLEN ?????????? SALESMAN

?????????? ? 3 ????? WARD ??????????? SALESMAN

?????????? ? 3 ????? MARTIN ????????? SALESMAN

?????????? ? 3 ????? TURNER ????????? SALESMAN

?????????? ? 3 ????? JAMES ?????????? CLERK

?

?????? ? LEVEL Ename ??????????????? JOB

??? ---------- -------------------- ---------

?????????? ? 2 ??? CLARK ???????????? MANAGER

?????????? ? 3 ????? MILLER ????????? CLERK

?

??? 13 rows selected .

????

??? -- 通過將過濾條件由 where 子句的內容移動到 connect by prior 子句中過濾掉 SCOTT 及其下屬

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename"

??? ? 3 ??? , job

??? ? 4 ? from emp

??? ? 5 ? start with empno = 7839

??? ? 6 ? connect by prior empno = mgr and ename != 'SCOTT' ;

?

?????? ? LEVEL Ename ??????????????? JOB

??? ---------- -------------------- ---------

?????????? ? 1 ? KING ??????????????? PRESIDENT

?????????? ? 2 ??? JONES ???????????? MANAGER

?????????? ? 3 ????? FORD ??????????? ANALYST

?????????? ? 4 ??????? SMITH ???????? CLERK

?????????? ? 2 ??? BLAKE ???????????? MANAGER

?????????? ? 3 ????? ALLEN ?????????? SALESMAN

?????????? ? 3 ????? WARD ??????????? SALESMAN

?????????? ? 3 ????? MARTIN ????????? SALESMAN

?????????? ? 3 ????? TURNER ????????? SALESMAN

?????????? ? 3 ????? JAMES ?????????? CLERK

?????????? ? 2 ??? CLARK ???????????? MANAGER

?

?????? ? LEVEL Ename ??????????????? JOB

??? ---------- -------------------- ---------

?????????? ? 3 ????? MILLER ????????? CLERK

?

??? 12 rows selected .

???

??? -- 在層次化查詢中增加過濾條件或使用子查詢

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename"

??? ? 3 ??? , job

??? ? 4 ? from emp

??? ? 5 ? where sal > 2500

??? ? 6 ? start with empno = 7839

??? ? 7 ? connect by prior empno = mgr ?????????????????????

??? ? 8 ? ;

?

?????? ? LEVEL Ename ??????????????? JOB

??? ---------- -------------------- ---------

?????????? ? 1 ? KING ??????????????? PRESIDENT

?????????? ? 2 ??? JONES ???????????? MANAGER

?????????? ? 3 ????? SCOTT ?????????? ANALYST

?????????? ? 3 ????? FORD ??????????? ANALYST

?????????? ? 2 ??? BLAKE ???????????? MANAGER

?????????? ?

??? SQL > select level ,

??? ? 2 ??? lpad ( ' ' , 2 * level - 1 ) || ename as "Ename"

??? ? 3 ??? , job

??? ? 4 ? from emp

??? ? 5 ? where sal > ( select avg ( sal ) from emp )

??? ? 6 ? start with empno = 7839

??? ? 7 ? connect by prior empno = mgr ;

?

?????? ? LEVEL Ename ??????????????? JOB

??? ---------- -------------------- ---------

?????????? ? 1 ? KING ??????????????? PRESIDENT

?????????? ? 2 ??? JONES ???????????? MANAGER

?????????? ? 3 ????? SCOTT ?????????? ANALYST

?????????? ? 3 ????? FORD ??????????? ANALYST

?????????? ? 2 ??? BLAKE ???????????? MANAGER

?????????? ? 2 ??? CLARK ???????????? MANAGER

?

??? 6 rows selected .

???

??? 更多參考：

?

Oracle 數據庫實例啟動關閉過程

?

Oracle 10g SGA 的自動化管理

?

使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實例

?

Oracle 實例和 Oracle 數據庫 (Oracle 體系結構 )

?

SQL 基礎 --> 常用函數

?

SQL 基礎 --> 過濾和排序

?

SQL 基礎 -->SELECT 查詢

?

?

?

SQL基礎-->層次化查詢(START BY ... CONNECT BY PRIOR)