Oracle遞歸查詢connect?by用法
一、概述
- Oracle中可以通過
START WITH . . .
CONNECT BY . . .
子句來實(shí)現(xiàn)SQL的層次查詢. - 自從Oracle 9i開始,可以通過
SYS_CONNECT_BY_PATH
函數(shù)實(shí)現(xiàn)將父節(jié)點(diǎn)到當(dāng)前行內(nèi)容以“path”或者層次元素列表的形式顯示出來。 - 自從Oracle 10g 中,還有其他更多關(guān)于層次查詢的新特性 。例如,有的時(shí)候用戶更關(guān)心的是每個(gè)層次分支中等級(jí)最低的內(nèi)容。
那么你就可以利用偽列函數(shù)CONNECT_BY_ISLEAF
來判斷當(dāng)前行是不是葉子。如果是葉子就會(huì)在偽列中顯示“1”,
如果不是葉子而是一個(gè)分支(例如當(dāng)前內(nèi)容是其他行的父親)就顯示“0”。 - 在Oracle 10g 之前的版本中,如果在你的樹中出現(xiàn)了環(huán)狀循環(huán)(如一個(gè)孩子節(jié)點(diǎn)引用一個(gè)父親節(jié)點(diǎn)),
Oracle 就會(huì)報(bào)出一個(gè)錯(cuò)誤提示:“ ORA-01436: CONNECT BY loop in user data”。如果不刪掉對(duì)父親的引用就無法執(zhí)行查詢操作。
而在 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”。
1、層級(jí)查詢的基本語法:
select [level],* feom table_name start with 條件1 connect by [ nocycle ] prior 條件2 where 條件3 ORDER BY [ sibilings ] 排序字段
說明:
- start with condition1 是用來限制第一層的數(shù)據(jù),或者叫根節(jié)點(diǎn)數(shù)據(jù);以這部分?jǐn)?shù)據(jù)為基礎(chǔ)來查找第二層數(shù)據(jù),然后以第二層數(shù)據(jù)查找第三層數(shù)據(jù)以此類推。
- connect by [prior] id=parentid 連接條件,目的就是給出父子之間的關(guān)系是什么,根據(jù)這個(gè)關(guān)系進(jìn)行遞歸查詢
- where 條件3---過濾條件,對(duì)所有返回的記錄進(jìn)行過濾。
- order by 排序字段---對(duì)所有返回記錄進(jìn)行排序
- 對(duì)prior說明:
- connect by prior dept_id=par_dept_id :采用自上而下的搜索方式(先找父節(jié)點(diǎn)然后找葉子節(jié)點(diǎn)),比如說查找第二層的數(shù)據(jù)時(shí)用第一層數(shù)據(jù)的id去跟表里面記錄的parentid字段進(jìn)行匹配,匹配成功那么查找出來的就是第二層數(shù)據(jù);
- connect by dept_id=prior par_dept_id:采用自下而上的搜索方式(先找葉子節(jié)點(diǎn)然后找父節(jié)點(diǎn))。 比如說用第一層數(shù)據(jù)的parentid去跟表記錄里面的id進(jìn)行匹配,匹配成功那么查找出來的就是第二層數(shù)據(jù);
- level關(guān)鍵字,LEVEL---偽列,用于表示樹的層次 ,第一層是數(shù)字1,第二層數(shù)字2,依次遞增。
- CONNECT_BY_ROOT方法,能夠獲取第一層集結(jié)點(diǎn)結(jié)果集中的任意字段的值;例CONNECT_BY_ROOT(字段名)。
二、使用
1、基本用法
例1、 查詢Raphaely及其的所有下屬
select * from employees start with last_name = 'Raphaely' connect by prior employee_id = manager_id; --找下屬 -- connect by employee_id = prior manager_id; --找上司,第一種,修改prior關(guān)鍵字位置 -- connect by prior manager_id = employee_id; --找上司,第二種,prior關(guān)鍵字不動(dòng) 調(diào)換后面的 employee_id = manager_id 邏輯關(guān)系的順序
例2、 查詢除了Raphaely和他下屬的所有員工
select * from employees start with manager_id is null connect by prior employee_id = manager_id and last_name <> 'Raphaely';
例3、 統(tǒng)計(jì)樹形的層數(shù)
select count(distinct LEVEL) from EMPLOYEES start with MANAGER_ID is null connect by prior EMPLOYEE_ID = MANAGER_ID;
例4、 過濾某些結(jié)果集,注意:where子句比connect by后執(zhí)行。
查詢Kochhar的所有下屬中l(wèi)astname為 Mavris雇員。
SELECT * FROM employees WHERE last_name = 'Mavris' START WITH last_name = 'Kochhar' --Kochhar的所有雇員 CONNECT BY PRIOR employee_id = manager_id;
例5、level偽列的使用,格式化層級(jí)
select lpad(' ',level*2,' ')||emp_name as name,emp_id,manager_id,salary,level from employee start with manager_id=0 connect by prior emp_id=manager_id
2、SYS_CONNECT_BY_PATH() 函數(shù)
作用: 將父節(jié)點(diǎn)到當(dāng)前節(jié)點(diǎn)的路徑按照指定的模式展現(xiàn)出來,把一個(gè)父節(jié)點(diǎn)下的所有節(jié)點(diǎn)通過某個(gè)字符區(qū)分,然后鏈接在一個(gè)列中顯示。
格式:
sys_connect_by_path(<列名>,<連接串>)
select sys_connect_by_path(t.dept_name,'-->'),t.dept_id, t.dept_name, t.dept_code,t.par_dept_id, level from SYS_DEPT t start with t.dept_id = 'e01d6' connect by prior t.dept_id = t.par_dept_id order by level, t.dept_code
3、CONNECT_BY_ISLEAF 偽列
作用:判斷層次查詢結(jié)果集中的行是不是葉子節(jié)點(diǎn)
返回值: 0表示不是葉子節(jié)點(diǎn), 1表示是葉子節(jié)點(diǎn)
例:
4、CONNECT_BY_ROOT 字段x -> 找到該節(jié)點(diǎn)最頂端節(jié)點(diǎn)的字段x
select last_name "Employee", connect_by_root last_name "Manager",sys_connect_by_path(last_name, ' -> ') "Path" from hr.employees where level > 1 connect by prior employee_id = manager_id order by last_name, length("Path");
思考? 為什么不能加 start with ? 加了會(huì)有什么效果?
不加start with , 則每個(gè)節(jié)點(diǎn)都遍歷一次 , connect_by_root 找到頂端的經(jīng)理人會(huì)不同
而加了start with manager_id is null 則從樹的根節(jié)點(diǎn) King 開始遍歷, 從而connect_by_root每個(gè)人的頂端的經(jīng)理都是King
5、10g新特性 采用sibilings排序
作用: 因?yàn)槭褂胦rder by排序會(huì)破壞層次,所以在oracle10g中,增加了siblings關(guān)鍵字的排序給葉子節(jié)點(diǎn)的關(guān)鍵字排序。
語法:
order siblings by <expre> asc|desc ;
它會(huì)保護(hù)層次,并且在每個(gè)等級(jí)中按expre排序
注意: order siblings by 必須緊跟著connect by,所以不能再用order by 了
例子:用order by,最后的結(jié)果是嚴(yán)格按照salary排序的,這樣把層級(jí)關(guān)系都打亂了
select t.employee_id,t.manager_id,t.first_name,t.salary, sys_connect_by_path(t.first_name, '->'), level from hr.employees t start with manager_id is null connect by prior employee_id = manager_id order by salary desc;
采用sibilings排序:結(jié)果的樹結(jié)構(gòu)沒有被打亂,且沒層級(jí)的sibilings都是按照salary排序的。
select t.employee_id,t.manager_id,t.first_name,t.salary,sys_connect_by_path(t.first_name, '->'),level from hr.employees t start with manager_id is null connect by prior employee_id = manager_id order siblings by salary desc;
三、與row num 生成序列記錄
rownum可用level代替。
1、簡單序列:
select rownum from dual connect by rownum<=4
1
2
3
4
2、生成10-14的連續(xù)數(shù)(10開始,5行數(shù)據(jù))
select 10+(rownum-1) from dual connect by rownum<=14-10+1
3、生成a-d的四個(gè)字母
select chr(ascii('a')+(rownum-1)) from dual connect by rownum<=ascii('d')-ascii('a')+1
4、生成2011-01-05至2011-01-10的日期
select to_date('2011-01-05','yyyy-mm-dd')+(rownum-1) from dual connect by rownum<=to_date('2011-01-10','yyyy-mm-dd')-to_date('2011-01-05','yyyy-mm-dd')+1
查詢當(dāng)前時(shí)間往前的12周的開始時(shí)間、結(jié)束時(shí)間、第多少周
select sysdate - (to_number(to_char(sysdate - 1, 'd')) - 1) - (rownum - 1) * 7 as startDate, sysdate + (7 - to_number(to_char(sysdate - 1, 'd'))) - (rownum - 1) * 7 as endDate, to_number(to_char(sysdate, 'iw')) - rownum + 1 as weekIndex from dual connect by level<= 12;--將level改成rownum可以實(shí)現(xiàn)同樣的效果
- d 表示一星期中的第幾天
- iw 表示一年中的第幾周
5、字符串分割,由一行變?yōu)槎嘈小?/h3>
生成a1,b1,d1序列
select substr(id, instr(id,',',1,rownum)+1, instr(id,',',1,rownum+1) - instr(id,',',1,rownum)-1)--根據(jù)逗號(hào)的位置進(jìn)行拆分 from (select ','||'a1,b1,d1'||',' as id from dual) --前后各加一個(gè)逗號(hào) connect by rownum<=length(id)-length(replace(id,',',''))-1
或者
select REGEXP_SUBSTR('a1,b1,d1', '[^,]+', 1, rownum) as newport from dual connect by rownum <= REGEXP_COUNT('a1,b1,d1', '[^,]+');
6、利用with子句生成測試數(shù)據(jù)
with temp as (select 'a' as A,'b' as B from dual union select 'c' as C,'d' as D from dual ) select * from temp;
7、日期維度數(shù)據(jù)生成方法。
select to_date('2011-01-05','yyyy-mm-dd')+(rownum-1) as ydate_date, to_char(to_date('2011-01-05','yyyy-mm-dd')+(rownum-1),'yyyy') as ydate_month from dba.tab_cols where to_date('2011-01-05','yyyy-mm-dd')+(rownum-1) != to_date('2060-01-05','yyyy-mm-dd')
到此這篇關(guān)于Oracle遞歸查詢connect by的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助,也希望大家多多支持腳本之家。
相關(guān)文章
[Oracle] 如何使用觸發(fā)器實(shí)現(xiàn)IP限制用戶登錄
在Oracle里,不像MySQL那樣方便,可以直接在用戶上進(jìn)行IP限制,Oracle要實(shí)現(xiàn)用戶級(jí)別的IP限制,可以使用觸發(fā)器來迂回實(shí)現(xiàn),以下就是示例,需要的朋友可以參考下2013-07-07Oracle 中 table 函數(shù)的應(yīng)用淺析
表函數(shù)可接受查詢語句或游標(biāo)作為輸入?yún)?shù),并可輸出多行數(shù)據(jù)。這篇文章主要介紹了Oracle 中 table 函數(shù)的應(yīng)用淺析,需要的朋友可以參考下2016-12-12Oracle如何實(shí)現(xiàn)like多個(gè)值的查詢
這篇文章主要給大家介紹了關(guān)于Oracle如何實(shí)現(xiàn)like多個(gè)值的查詢的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-08-08Oracle數(shù)據(jù)庫服務(wù)器修改操作系統(tǒng)時(shí)間的注意事項(xiàng)詳解
在本篇文章里小編給大家整理的是一篇關(guān)于Oracle數(shù)據(jù)庫服務(wù)器修改操作系統(tǒng)時(shí)間的注意事項(xiàng)詳解,需要的朋友們可以參考下。2020-03-03oracle獲取當(dāng)前時(shí)間,精確到毫秒并指定精確位數(shù)的實(shí)現(xiàn)方法
下面小編就為大家?guī)硪黄猳racle獲取當(dāng)前時(shí)間,精確到毫秒并指定精確位數(shù)的實(shí)現(xiàn)方法。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-05-05Oracle中在pl/sql developer修改表的2種方法
Oracle中在pl/sql developer修改表的2種方法,需要的朋友可以參考一下2013-03-03linux下oracle設(shè)置開機(jī)自啟動(dòng)實(shí)現(xiàn)方法
你會(huì)發(fā)現(xiàn)Oracle沒有自行啟動(dòng),這是正常的,因?yàn)樵贚inux下安裝Oracle的確不會(huì)自行啟動(dòng),必須要自行設(shè)定相關(guān)參數(shù),首先先介紹一般而言如何啟動(dòng)oracle2012-11-11ORACLE應(yīng)用經(jīng)驗(yàn)(2)
ORACLE應(yīng)用經(jīng)驗(yàn)(2)...2007-03-03