connect by遞歸查詢基本語法是：
在桂陽等地區(qū)，都構(gòu)建了全面的區(qū)域性戰(zhàn)略布局，加強(qiáng)發(fā)展的系統(tǒng)性、市場前瞻性、產(chǎn)品創(chuàng)新能力，以專注、極致的服務(wù)理念，為客戶提供網(wǎng)站設(shè)計(jì)、成都網(wǎng)站設(shè)計(jì) 網(wǎng)站設(shè)計(jì)制作按需求定制網(wǎng)站,公司網(wǎng)站建設(shè),企業(yè)網(wǎng)站建設(shè),成都品牌網(wǎng)站建設(shè),全網(wǎng)整合營銷推廣,外貿(mào)網(wǎng)站建設(shè),桂陽網(wǎng)站建設(shè)費(fèi)用合理。
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)鍵字可以放在等號(hào)的前面，也可以放在等號(hào)的后面，表示的意義是不一樣的，比如 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)的單位，不能查子級(jí)單位
oracle 遞歸查詢start with connect by prior的用法和知識(shí)不僅僅這些，本博客只是簡單記錄一下我所遇到的，網(wǎng)上發(fā)現(xiàn)一篇寫的比較詳細(xì)的博客，在Linux公社， ，
oracle方面的一些知識(shí)也可以參考我之前的一篇博客：
首先說一下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中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)存儲(chǔ)在一張表里，比如一個(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表示該雇員的編號(hào)，MGR表示領(lǐng)導(dǎo)該雇員的人的編號(hà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)識(shí)哪個(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被放置于等號(hào)前后的位置，決定著查詢時(shí)的檢索順序。
PRIOR被置于CONNECT BY子句中等號(hào)的前面時(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 子句中等號(hào)的后面時(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è)層號(hào)。層號(hào)根據(jù)節(jié)點(diǎn)與根節(jié)點(diǎn)的距離確定。不論從哪個(gè)節(jié)點(diǎn)開始，該起始根節(jié)點(diǎn)的層號(hào)始終為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)的順序。
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
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ì)以全部列作為下一層級(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
重慶分公司
重慶分公司
