一、概述

• Oracle中可以通過(guò)START WITH . . . CONNECT BY . . .子句來(lái)實(shí)現(xiàn)SQL的層次查詢.
• 自從Oracle 9i開(kāi)始，可以通過(guò) SYS_CONNECT_BY_PATH 函數(shù)實(shí)現(xiàn)將父節(jié)點(diǎn)到當(dāng)前行內(nèi)容以“path”或者層次元素列表的形式顯示出來(lái)。
• 自從Oracle 10g 中，還有其他更多關(guān)于層次查詢的新特性 。例如，有的時(shí)候用戶更關(guān)心的是每個(gè)層次分支中等級(jí)最低的內(nèi)容。 
  那么你就可以利用偽列函數(shù)CONNECT_BY_ISLEAF來(lái)判斷當(dāng)前行是不是葉子。如果是葉子就會(huì)在偽列中顯示“1”， 
  如果不是葉子而是一個(gè)分支（例如當(dāng)前內(nèi)容是其他行的父親）就顯示“0”。
• 在Oracle 10g 之前的版本中，如果在你的樹(shù)中出現(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ì)父親的引用就無(wú)法執(zhí)行查詢操作。 
  而在 Oracle 10g 中，只要指定“NOCYCLE”就可以進(jìn)行任意的查詢操作。與這個(gè)關(guān)鍵字相關(guān)的還有一個(gè)偽列——CONNECT_BY_ISCYCLE， 
  如果在當(dāng)前行中引用了某個(gè)父親節(jié)點(diǎn)的內(nèi)容并在樹(shù)中出現(xiàn)了循環(huán)，那么該行的偽列中就會(huì)顯示“1”，否則就顯示“0”。

1、層級(jí)查詢的基本語(yǔ)法：

select [level],*　　feom table_name 　
start with 條件1
connect by [ nocycle ]  prior 條件2
where 條件3　ORDER　BY [ sibilings ]　排序字段

說(shuō)明：

• start with condition1 是用來(lái)限制第一層的數(shù)據(jù)，或者叫根節(jié)點(diǎn)數(shù)據(jù)；以這部分?jǐn)?shù)據(jù)為基礎(chǔ)來(lái)查找第二層數(shù)據(jù)，然后以第二層數(shù)據(jù)查找第三層數(shù)據(jù)以此類推。
• connect by [prior] id=parentid 連接條件，目的就是給出父子之間的關(guān)系是什么，根據(jù)這個(gè)關(guān)系進(jìn)行遞歸查詢
• where 條件3---過(guò)濾條件，對(duì)所有返回的記錄進(jìn)行過(guò)濾。
• order by 排序字段---對(duì)所有返回記錄進(jìn)行排序
• 對(duì)prior說(shuō)明：
  • connect by prior dept_id=par_dept_id ：采用自上而下的搜索方式（先找父節(jié)點(diǎn)然后找葉子節(jié)點(diǎn)），比如說(shuō)查找第二層的數(shù)據(jù)時(shí)用第一層數(shù)據(jù)的id去跟表里面記錄的parentid字段進(jìn)行匹配，匹配成功那么查找出來(lái)的就是第二層數(shù)據(jù)；
  • connect by dept_id=prior par_dept_id：采用自下而上的搜索方式（先找葉子節(jié)點(diǎn)然后找父節(jié)點(diǎn)）。 比如說(shuō)用第一層數(shù)據(jù)的parentid去跟表記錄里面的id進(jìn)行匹配，匹配成功那么查找出來(lái)的就是第二層數(shù)據(jù)；
• level關(guān)鍵字，LEVEL---偽列，用于表示樹(shù)的層次 ，第一層是數(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ù)形的層數(shù)

select count(distinct LEVEL)   from EMPLOYEES
 start with MANAGER_ID is null
 connect by prior EMPLOYEE_ID = MANAGER_ID;

例4、 過(guò)濾某些結(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)出來(lái)，把一個(gè)父節(jié)點(diǎn)下的所有節(jié)點(diǎn)通過(guò)某個(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 則從樹(shù)的根節(jié)點(diǎn) King 開(kāi)始遍歷, 從而connect_by_root每個(gè)人的頂端的經(jīng)理都是King

5、10g新特性 采用sibilings排序

作用: 因?yàn)槭褂胦rder by排序會(huì)破壞層次，所以在oracle10g中，增加了siblings關(guān)鍵字的排序給葉子節(jié)點(diǎn)的關(guān)鍵字排序。

語(yǔ)法：

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é)果的樹(shù)結(jié)構(gòu)沒(méi)有被打亂,且沒(méi)層級(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、簡(jiǎn)單序列：

select rownum from dual connect by rownum<=4

1 
2 
3 
4

2、生成10-14的連續(xù)數(shù)（10開(kāi)始，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周的開(kāi)始時(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子句生成測(cè)試數(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í)有所幫助，也希望大家多多支持腳本之家。