ORACLE遞歸

about connect by

成都創(chuàng)新互聯(lián)長期為1000+客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為富平企業(yè)提供專業(yè)的成都網(wǎng)站建設(shè)、網(wǎng)站建設(shè)，富平網(wǎng)站改版等技術(shù)服務(wù)。擁有十多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

SELECT empno, ename, job, mgr, deptno, LEVEL, sys_connect_by_path(ename,'\'), connect_by_root(ename) FROM emp START WITH mgr IS NULL CONNECT BY mgr =? PRIOR empno

WITH T(empno, ename, job, mgr, deptno, the_level, path,top_manager) AS ( ---- 必須把結(jié)構(gòu)寫出來

SELECT empno, ename, job, mgr, deptno? ---- 先寫錨點(diǎn)查詢，用START WITH的條件

,1 AS the_level? ? ---- 遞歸起點(diǎn)，第一層

,'\'||ename? ? ? ? ---- 路徑的第一截

,ename AS top_manager ---- 原來的CONNECT_BY_ROOT

FROM scott.EMP

WHERE mgr IS NULL ---- 原來的START WITH條件

UNION ALL? ---- 下面是遞歸部分

SELECT e.empno, e.ename, e.job, e.mgr, e.deptno? ---- 要加入的新一層數(shù)據(jù)，來自要遍歷的emp表

,1 + t.the_level? ? ? ? ? ? ---- 遞歸層次，在原來的基礎(chǔ)上加1。這相當(dāng)于CONNECT BY查詢中的LEVEL偽列

,t.path||'\'||e.ename? ? ? ? ---- 把新的一截路徑拼上去

,t.top_manager? ? ? ? ? ? ? ---- 直接繼承原來的數(shù)據(jù)，因?yàn)槊總€(gè)路徑的根節(jié)點(diǎn)只有一個(gè)

FROM t, scott.emp e? ? ? ? ? ? ? ? ? ? ---- 典型寫法，把子查詢本身和要遍歷的表作一個(gè)連接

WHERE t.empno = e.mgr? ? ? ? ? ? ---- 原來的CONNECT BY條件

) ---- WITH定義結(jié)束

SELECT * FROM T

EMPNO ENAME? ? ? JOB? ? ? ? MGR DEPTNO? THE_LEVEL PATH? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TOP_MANAGER

----- ---------- --------- ----- ------ ---------- -------------------------------------------------------------------------------- -----------

7839 KING? ? ? PRESIDENT? ? ? ? ? 10? ? ? ? ? 1 \KING? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7566 JONES? ? ? MANAGER? ? 7839? ? 20? ? ? ? ? 2 \KING\JONES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7698 BLAKE? ? ? MANAGER? ? 7839? ? 30? ? ? ? ? 2 \KING\BLAKE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7782 CLARK? ? ? MANAGER? ? 7839? ? 10? ? ? ? ? 2 \KING\CLARK? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7999 MIKE? ? ? ANALYST? ? 7566? ? 30? ? ? ? ? 3 \KING\JONES\MIKE? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7499 ALLEN? ? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\ALLEN? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7521 WARD? ? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\WARD? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7654 MARTIN? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\MARTIN? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7788 SCOTT? ? ? ANALYST? ? 7566? ? 20? ? ? ? ? 3 \KING\JONES\SCOTT? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7844 TURNER? ? SALESMAN? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\TURNER? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7900 JAMES? ? ? CLERK? ? ? 7698? ? 30? ? ? ? ? 3 \KING\BLAKE\JAMES? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7902 FORD? ? ? ANALYST? ? 7566? ? 20? ? ? ? ? 3 \KING\JONES\FORD? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7934 MILLER? ? CLERK? ? ? 7782? ? 10? ? ? ? ? 3 \KING\CLARK\MILLER? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7369 SMITH? ? ? CLERK? ? ? 7902? ? 20? ? ? ? ? 4 \KING\JONES\FORD\SMITH? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

7876 ADAMS? ? ? CLERK? ? ? 7788? ? 20? ? ? ? ? 4 \KING\JONES\SCOTT\ADAMS? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? KING

Oracle遞歸查詢：使用prior實(shí)現(xiàn)樹操作

select * from tableName

start with ?條件A ? -- 開始遞歸的根節(jié)點(diǎn),可多個(gè)條件

connect ?by prior ?條件B ?--?prior ?決定查詢的索引順序

where 條件 C

select t.empno,t.mgr,t.deptno ,level

from emp t

connect by prior t.empno=t.mgr

order by level,t.mgr,t.deptno;

找到empno為7369的所有領(lǐng)導(dǎo)。

select t.*,t.rowid from emp t

start with t.empno = 7369 ? ? ? --從empno為7369的開始查找

connect by prior t.mgr = t.empno ;? ? --上一條數(shù)據(jù)（這里就是empno為7369）的mgr == 當(dāng)前遍歷這一條數(shù)據(jù)的empno（那么就會(huì)找到empno為7902的用戶）

找到empno為7566的所有下屬

select t.*,t.rowid from emp t

start with t.empno = 7566

connect by prior t.empno = t.mgr ; --注意：connect by? t.mgr =prior t.empno與左邊寫法含義一樣

start with ：設(shè)置起點(diǎn)，省略后默認(rèn)以全部行為起點(diǎn)。

connect by [condition] ：與一般的條件一樣作用于當(dāng)前列，但是在滿足條件后，會(huì)以全部列作為下一層級遞歸（沒有其他條件的話）。

prior ： 表示上一層級的標(biāo)識符。經(jīng)常用來對下一層級的數(shù)據(jù)進(jìn)行限制。不可以接偽列。

level ：偽列，表示當(dāng)前深度。

connect_by_root() ：顯示根節(jié)點(diǎn)列。經(jīng)常用來分組。

connect_by_isleaf ：1是葉子節(jié)點(diǎn)，0不是葉子節(jié)點(diǎn)。在制作樹狀表格時(shí)必用關(guān)鍵字。

sys_connect_by_path() ：將遞歸過程中的列進(jìn)行拼接。

nocycle , connect_by_iscycle : 在有循環(huán)結(jié)構(gòu)的查詢中使用。

siblings : 保留樹狀結(jié)構(gòu)，對兄弟節(jié)點(diǎn)進(jìn)行排序

;request_id=162538763316780265474850biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-22-52652111.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187

;request_id=162538763316780269872688biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~baidu_landing_v2~default-5-108683534.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187

;request_id=162538763316780265474850biz_id=0utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-10-105773226.first_rank_v2_pc_rank_v29_1utm_term=ORACLE%E9%80%92%E5%BD%92%E5%87%BD%E6%95%B0spm=1018.2226.3001.4187

Oracle遞歸查詢start with connect by prior

connect by遞歸查詢基本語法是：

start with：表示以什么為根節(jié)點(diǎn)，不加限制可以寫1=1，要以id為123的節(jié)點(diǎn)為根節(jié)點(diǎn)，就寫為start with id =123

connect by：connect by是必須的，start with有些情況是可以省略的，或者直接start with 1=1不加限制

prior：prior關(guān)鍵字可以放在等號的前面，也可以放在等號的后面，表示的意義是不一樣的，比如 prior id = pid，就表示id就是這條記錄的根節(jié)點(diǎn)了

舉個(gè)例子，寫條SQL：

t_user(用戶表)，t_unit_info(單位表)，意思是以單位編碼為"15803"的單位為根節(jié)點(diǎn)，查找出其單位及其子單位的用戶信息

然后將prior換一下位置，發(fā)現(xiàn)只能查出單位編碼為"15803"對應(yīng)的單位，不能查子級單位

oracle 遞歸查詢start with connect by prior的用法和知識不僅僅這些，本博客只是簡單記錄一下我所遇到的，網(wǎng)上發(fā)現(xiàn)一篇寫的比較詳細(xì)的博客，在Linux公社， ，

oracle方面的一些知識也可以參考我之前的一篇博客：

oracle遞歸查詢

oracle遞歸查詢

Oracle中start by prior子句用法

connect by 是結(jié)構(gòu)化查詢中用到的，其基本語法是：

select ... from tablename start with 條件1

connect by 條件2

where 條件3;

例：

select * from table

start with org_id = 'HBHqfWGWPy'

connect by prior org_id = parent_id;

簡單說來是將一個(gè)樹狀結(jié)構(gòu)存儲在一張表里，比如一個(gè)表中存在兩個(gè)字段:

org_id,parent_id那么通過表示每一條記錄的parent是誰，就可以形成一個(gè)樹狀結(jié)構(gòu)。

用上述語法的查詢可以取得這棵樹的所有記錄。

其中：

條件1 是根結(jié)點(diǎn)的限定語句，當(dāng)然可以放寬限定條件，以取得多個(gè)根結(jié)點(diǎn)，實(shí)際就是多棵樹。

條件2 是連接條件，其中用PRIOR表示上一條記錄，比如 CONNECT BY PRIOR org_id = parent_id就是說上一條記錄的org_id 是本條記錄的parent_id，即本記錄的父親是上一條記錄。

條件3 是過濾條件，用于對返回的所有記錄進(jìn)行過濾。

簡單介紹如下：

早掃描樹結(jié)構(gòu)表時(shí)，需要依此訪問樹結(jié)構(gòu)的每個(gè)節(jié)點(diǎn)，一個(gè)節(jié)點(diǎn)只能訪問一次，其訪問的步驟如下：

第一步：從根節(jié)點(diǎn)開始；

第二步：訪問該節(jié)點(diǎn)；

第三步：判斷該節(jié)點(diǎn)有無未被訪問的子節(jié)點(diǎn)，若有，則轉(zhuǎn)向它最左側(cè)的未被訪問的子節(jié)，并執(zhí)行第二步，否則執(zhí)行第四步；

第四步：若該節(jié)點(diǎn)為根節(jié)點(diǎn)，則訪問完畢，否則執(zhí)行第五步；

第五步：返回到該節(jié)點(diǎn)的父節(jié)點(diǎn)，并執(zhí)行第三步驟。

總之：掃描整個(gè)樹結(jié)構(gòu)的過程也即是中序遍歷樹的過程。

1． 樹結(jié)構(gòu)的描述

樹結(jié)構(gòu)的數(shù)據(jù)存放在表中，數(shù)據(jù)之間的層次關(guān)系即父子關(guān)系，通過表中的列與列間的關(guān)系來描述，如EMP表中的EMPNO和MGR。EMPNO表示該雇員的編號，MGR表示領(lǐng)導(dǎo)該雇員的人的編號，即子節(jié)點(diǎn)的MGR值等于父節(jié)點(diǎn)的EMPNO值。在表的每一行中都有一個(gè)表示父節(jié)點(diǎn)的MGR（除根節(jié)點(diǎn)外），通過每個(gè)節(jié)點(diǎn)的父節(jié)點(diǎn)，就可以確定整個(gè)樹結(jié)構(gòu)。

在SELECT命令中使用CONNECT BY 和藹START WITH 子句可以查詢表中的樹型結(jié)構(gòu)關(guān)系。其命令格式如下：

SELECT 。。。

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

[START WITH]；

其中：CONNECT BY子句說明每行數(shù)據(jù)將是按層次順序檢索，并規(guī)定將表中的數(shù)據(jù)連入樹型結(jié)構(gòu)的關(guān)系中。PRIORY運(yùn)算符必須放置在連接關(guān)系的兩列中某一個(gè)的前面。對于節(jié)點(diǎn)間的父子關(guān)系，PRIOR運(yùn)算符在一側(cè)表示父節(jié)點(diǎn)，在另一側(cè)表示子節(jié)點(diǎn)，從而確定查找樹結(jié)構(gòu)是的順序是自頂向下還是自底向上。在連接關(guān)系中，除了可以使用列名外，還允許使用列表達(dá)式。START WITH 子句為可選項(xiàng)，用來標(biāo)識哪個(gè)節(jié)點(diǎn)作為查找樹型結(jié)構(gòu)的根節(jié)點(diǎn)。若該子句被省略，則表示所有滿足查詢條件的行作為根節(jié)點(diǎn)。

START WITH： 不但可以指定一個(gè)根節(jié)點(diǎn)，還可以指定多個(gè)根節(jié)點(diǎn)。

2． 關(guān)于PRIOR

運(yùn)算符PRIOR被放置于等號前后的位置，決定著查詢時(shí)的檢索順序。

PRIOR被置于CONNECT BY子句中等號的前面時(shí)，則強(qiáng)制從根節(jié)點(diǎn)到葉節(jié)點(diǎn)的順序檢索，即由父節(jié)點(diǎn)向子節(jié)點(diǎn)方向通過樹結(jié)構(gòu)，我們稱之為自頂向下的方式。如：

CONNECT BY PRIOR EMPNO=MGR

PIROR運(yùn)算符被置于CONNECT BY 子句中等號的后面時(shí)，則強(qiáng)制從葉節(jié)點(diǎn)到根節(jié)點(diǎn)的順序檢索，即由子節(jié)點(diǎn)向父節(jié)點(diǎn)方向通過樹結(jié)構(gòu)，我們稱之為自底向上的方式。例如：

CONNECT BY EMPNO=PRIOR MGR

在這種方式中也應(yīng)指定一個(gè)開始的節(jié)點(diǎn)。

3． 定義查找起始節(jié)點(diǎn)

在自頂向下查詢樹結(jié)構(gòu)時(shí)，不但可以從根節(jié)點(diǎn)開始，還可以定義任何節(jié)點(diǎn)為起始節(jié)點(diǎn)，以此開始向下查找。這樣查找的結(jié)果就是以該節(jié)點(diǎn)為開始的結(jié)構(gòu)樹的一枝。

4．使用LEVEL

在具有樹結(jié)構(gòu)的表中，每一行數(shù)據(jù)都是樹結(jié)構(gòu)中的一個(gè)節(jié)點(diǎn)，由于節(jié)點(diǎn)所處的層次位置不同，所以每行記錄都可以有一個(gè)層號。層號根據(jù)節(jié)點(diǎn)與根節(jié)點(diǎn)的距離確定。不論從哪個(gè)節(jié)點(diǎn)開始，該起始根節(jié)點(diǎn)的層號始終為1，根節(jié)點(diǎn)的子節(jié)點(diǎn)為2， 依此類推。圖1.2就表示了樹結(jié)構(gòu)的層次。

5．節(jié)點(diǎn)和分支的裁剪

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

6．排序顯示

象在其它查詢中一樣，在樹結(jié)構(gòu)查詢中也可以使用ORDER BY 子句，改變查詢結(jié)果的顯示順序，而不必按照遍歷樹結(jié)構(gòu)的順序。

網(wǎng)站欄目：oracle怎么遞歸查詢 oracle遞歸查詢所有子節(jié)點(diǎn)
本文路徑：http://chinadenli.net/article40/hpepeo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供軟件開發(fā)、做網(wǎng)站、App開發(fā)、網(wǎng)站設(shè)計(jì)、ChatGPT、企業(yè)建站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)