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

select * from tableName

成都創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供曲松網(wǎng)站建設(shè)、曲松做網(wǎng)站、曲松網(wǎng)站設(shè)計(jì)、曲松網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、曲松企業(yè)網(wǎng)站模板建站服務(wù)，10余年曲松做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

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ì)以全部列作為下一層級(jí)遞歸（沒有其他條件的話）。

prior ： 表示上一層級(jí)的標(biāo)識(shí)符。經(jīng)常用來對下一層級(jí)的數(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 select 遞歸查詢

START WITH 定義數(shù)據(jù)行查詢的初始起點(diǎn)；

CONNECT BY prior 定義表中的各個(gè)行是如何聯(lián)系的；

connect by 后面的"prior" 如果缺省，則只能查詢到符合條件的起始行，并不進(jìn)行遞歸查詢；

條件2：col_1 = col_2，col_1是父鍵（它標(biāo)識(shí)父），col_2是子鍵（它標(biāo)識(shí)子）。

條件3過濾遞歸前相應(yīng)節(jié)點(diǎn)及其子節(jié)點(diǎn)，如果上級(jí)節(jié)點(diǎn)不滿足則下級(jí)節(jié)點(diǎn)自動(dòng)過濾掉；

條件4過濾遞歸后相應(yīng)的節(jié)點(diǎn)或子節(jié)點(diǎn)，如果上級(jí)節(jié)點(diǎn)不滿足則下級(jí)結(jié)點(diǎn)自動(dòng)提升一級(jí)。

系統(tǒng)偽列：

CURRVAL AND NEXTVAL 使用序列號(hào)的保留字

ROWID 記錄的唯一標(biāo)識(shí)

ROWNUM 限制查詢結(jié)果集的數(shù)量

LEVEL 顯示層次樹中特定行的層次或級(jí)別

CONNECT_BY_ROOT 返回當(dāng)前層的根節(jié)點(diǎn)(當(dāng)前行數(shù)據(jù)所對應(yīng)的最高等級(jí)節(jié)點(diǎn)的內(nèi)容)

SYS_CONNECT_BY_PATH(column, char) 函數(shù)實(shí)現(xiàn)將從父節(jié)點(diǎn)到當(dāng)前行內(nèi)容以"path"或者層次元素列表的形式顯示出來

CONNECT_BY_ISCYCLE 須帶參數(shù)NOCYCLE，當(dāng)前行中引用了某個(gè)父親節(jié)點(diǎn)的內(nèi)容并在樹中出現(xiàn)了循環(huán)，如果循環(huán)顯示"1"，否則就顯示"0"。

CONNECT_BY_ISLEAF 判斷當(dāng)前行是不是葉子。如果是葉子顯示"1"，如果不是葉子而是一個(gè)分支（例如當(dāng)前內(nèi)容是其他行的父親）就顯示"0"

而在 Oracle 10g 中，只要指定"NOCYCLE"就可以進(jìn)行任意的查詢操作。與這個(gè)關(guān)鍵字相關(guān)的還有一個(gè)偽列——CONNECT_BY_ISCYCLE， 如果在當(dāng)前行中引用了某個(gè)父親節(jié)點(diǎn)的內(nèi)容并在樹中出現(xiàn)了循環(huán)，那么該行的偽列中就會(huì)顯示"1"，否則就顯示"0"。

【實(shí)例】

--創(chuàng)建測試表，增加測試數(shù)據(jù)

create table test(superid varchar2(20),id varchar2(20),mc varchar2(20));

insert into test values('0','1','A1');

insert into test values('0','2','A2');

insert into test values('1','11','A11');

insert into test values('1','12','A12');

insert into test values('2','21','A21');

insert into test values('2','22','A22');

insert into test values('11','111','A111');

insert into test values('11','112','A112');

insert into test values('12','121','A121');

insert into test values('12','122','A122');

insert into test values('21','211','A211');

insert into test values('21','212','A212');

insert into test values('22','221','A221');

insert into test values('22','222','A222');

commit;

--層次查詢示例

select level||'級(jí)' jc,lpad(' ',(level-1)*4)||id id,mc

from test

start with superid = '0' connect by prior id=superid;

select level||'級(jí)' jc,connect_by_isleaf mxf,lpad(' ',(level-1)*4)||id id,mc

from test

start with superid = '0' connect by prior id=superid;

--給出兩個(gè)以前在"數(shù)據(jù)庫字符串分組相加之四"中的例子來理解start with ... connect by ...

--功能：實(shí)現(xiàn)按照superid分組,把id用";"連接起來

--實(shí)現(xiàn)：以下兩個(gè)例子都是通過構(gòu)造2個(gè)偽列來實(shí)現(xiàn)connect by連接的。

oracle中SQL遞歸查詢

要看這個(gè)“下級(jí)人數(shù)”是怎么定義的，如果只是記錄中PID值是父類ID的人數(shù)之和，那么就用with，先按PID做一個(gè)統(tǒng)計(jì)結(jié)果集，然后用原本數(shù)據(jù)表作為查詢目標(biāo)表，左外連接with的結(jié)果集，并將人數(shù)相加；

如果是要所有下級(jí)的人數(shù)之和，則需要要遞歸函數(shù)來計(jì)算人數(shù)。

ORACLE遞歸

about connect by

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

sql語句實(shí)現(xiàn)遞歸查詢所有節(jié)點(diǎn),mysql和oracle都能用的

首先說一下Oracle的遞歸查詢，相信大部分人都知道很簡單。無非start with connect by 函數(shù)。下面是從pId向子節(jié)點(diǎn)遞歸查詢的例子，unId是數(shù)據(jù)庫表中的主鍵。

如果是從子節(jié)點(diǎn)遞歸到父節(jié)點(diǎn)查詢，就把start with 換成unid，prior左右對換

下面再講MySql 的遞歸查詢方式。MySql沒有Oracle的強(qiáng)大功能，雖然都是同一個(gè)公司的產(chǎn)品。所以只能靠自己寫。有很多方法，用sql去循環(huán)查詢，或者寫存儲(chǔ)過程，我這里只提供一種。就是新建一個(gè)function函數(shù)。

表結(jié)構(gòu)不說了，無非就是 Id ，pId，其他列。下面是創(chuàng)建一個(gè)遞歸查詢子節(jié)點(diǎn)的函數(shù)

DROP FUNCTION IF EXISTS queryChildrenPowerInfo;

CREATE FUNCTION `queryChildrenPowerInfo` (powerId VARCHAR(2000))

RETURNS VARCHAR(2000)

BEGIN

DECLARE sTemp VARCHAR(2000);

DECLARE sTempChd VARCHAR(2000);

SET sTemp = '$';

SET sTempChd = cast(powerId as CHAR);

WHILE sTempChd is not NULL DO

SET sTemp = CONCAT(sTemp, ',', sTempChd);

SELECT group_concat(id) INTO sTempChd FROM t_discretionary_power where FIND_IN_SET(pId,sTempChd)0;

END WHILE;

return sTemp;

END

調(diào)用的時(shí)候：select? queryChildrenPowerInfo("fa2528924c7e9168014c9bedfe04039c"); 該語句會(huì)返回Id和父Id等于傳入?yún)?shù)powerId的一個(gè)字符串，中間有逗號(hào)隔開如圖

下面這句代碼的意思是，查詢出 t_discretionary_power? 表中，t.id 等于上面查詢出的結(jié)果集的數(shù)據(jù)。FIND_IN_SET(A,B)是MYSQL的函數(shù)。意思是查找在B集合中有A的數(shù)據(jù)。相當(dāng)于In

select t.* from t_discretionary_power? t where FIND_IN_SET(t.id,queryChildrenPowerInfo('fa2528924c7e9168014c9bedfe04039c'))

文章名稱：oracle怎么求遞歸 oracle遞歸向上匯總
標(biāo)題網(wǎng)址：http://chinadenli.net/article4/hpisoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、定制開發(fā)、網(wǎng)站制作、移動(dòng)網(wǎng)站建設(shè)、小程序開發(fā)、標(biāo)簽優(yōu)化

廣告

聲明：本網(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)