久久综合久,亚洲精品一区二区三区不卡,污网站视频

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

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

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

??? 層次化查詢 , 即樹型結(jié)構(gòu)查詢 , 是 SQL 中經(jīng)常用到的功能之一 , 通常由根節(jié)點(diǎn) , 父節(jié)點(diǎn) , 子節(jié)點(diǎn) , 葉節(jié)點(diǎn)組成 , 其語法如下 :

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

?????? FROM table_name

?????? [WHERE where_clause]

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

??????

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

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

?????? prior_condition ：定義父節(jié)點(diǎn)和子節(jié)點(diǎn)之間的關(guān)系

???

??? -- 使用 start with ...connect by prior 從根節(jié)點(diǎn)開始遍歷

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

???

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

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

?????? 2 ). 遍歷根節(jié)點(diǎn) ( 得到 empno = 7839 記錄的相關(guān)信息 )

?????? 3 ). 判斷該節(jié)點(diǎn)是否存在由子節(jié)點(diǎn)，如果則訪問最左側(cè)未被訪問的子節(jié)點(diǎn) , 轉(zhuǎn)到 ), 否則下一步

?????? ??? 如上例中 prior_condition 為 empno = mgr , 即子節(jié)點(diǎn)的 mgr 等于父節(jié)點(diǎn)的 empno , 在此時(shí) mgr 為 7839 的記錄

?????? 4 ). 當(dāng)節(jié)點(diǎn)為葉節(jié)點(diǎn)，則訪問完畢 , 否則 , 轉(zhuǎn)到 )

?????? 5 ). 返回到該節(jié)點(diǎn)的父節(jié)點(diǎn) , 轉(zhuǎn)到 )

??????

??? -- 偽列 level 的使用

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

??? --prior 表示前一條記錄 , 即下一條返回記錄的 mgr 應(yīng)當(dāng)?shù)扔谇耙粭l記錄的 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

???

??? -- 獲得層次數(shù)

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

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

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

?????? ? Level

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

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

?????????? ?

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

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

???

??? -- 從非根節(jié)點(diǎn)開始遍歷 ( 只需修改 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應(yīng)當(dāng)?shù)扔谇耙粭l記錄的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 置于等號(hào)右邊 , 得到相同的結(jié)果 )

??? 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

?????????? ?

??? -- 從層次查詢中刪除節(jié)點(diǎn)和分支

??? 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 子句的內(nèi)容移動(dòng)到 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 10g SGA 的自動(dòng)化管理

使用 OEM,SQL*Plus,iSQL*Plus 管理 Oracle 實(shí)例

Oracle 實(shí)例和 Oracle 數(shù)據(jù)庫 (Oracle 體系結(jié)構(gòu) )

SQL 基礎(chǔ) --> 常用函數(shù)

SQL 基礎(chǔ) --> 過濾和排序

SQL 基礎(chǔ) -->SELECT 查詢

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

更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號(hào)聯(lián)系： 360901061

您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義