oracle connect by用法篇____oracle

1.基本文法

select * from table [start with condition1]    connect by [prior] id=parentid1

一般用來尋找存在父子關係的資料，也就是樹形結構的資料；其返還的資料也能夠明確的區分出每一層的資料。

start with condition1 是用來限制第一層的資料，或者叫根節點資料；以這部分資料為基礎來尋找第二層資料，然後以第二層資料尋找第三層資料以此類推。 connect by [prior] id=parentid 這部分是用來指明oracle在尋找資料時以怎樣的一種關係去尋找；比如說尋找第二層的資料時用第一層資料的id去跟表裡面記錄的parentid欄位進行匹配，如果這個條件成立那麼尋找出來的資料就是第二層資料，同理尋找第三層第四層…等等都是按這樣去匹配。

prior還有一種用法：

select * from table [start with condition1]    connect by id= [prior] parentid1

其他特性

level關鍵字，代表樹形結構中的層級編號；第一層是數字1，第二層數字2，依次遞增。 CONNECT_BY_ROOT方法，能夠擷取第一層集結點結果集中的任意欄位的值；例CONNECT_BY_ROOT（欄位名）。 2.demo例子

2.1 從根節點尋找葉子節點

select t.*, level, CONNECT_BY_ROOT(id)  from tab_test t start with t.id = 0connect by prior t.id = t.fid;

2.2 從葉子節點尋找上層節點

--第一種,修改prior關鍵字位置select t.*, level, CONNECT_BY_ROOT(id)  from tab_test t start with t.id = 4connect by t.id = prior t.fid;--第二種,prior關鍵字不動 調換後面的id=fid邏輯關係的順序select t.*, level, CONNECT_BY_ROOT(id)  from tab_test t start with t.id = 4connect by prior t.fid = t.id;

3.常用例子

3.1 查詢人員所屬的二級機構部門代碼

select distinct deptno    from emp_deptwhere dept_level = '2'     connect by dept_no = prior parent_dept_nostart with dept_no  in ( select  deptno  from emp where name='張珊'; )

3.2 字串分割

比如說分割01#02#03#04這種有規律的字串

select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport     from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');