oracle中connect by怎么用

這篇文章主要介紹了oracle中connect by怎么用，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

1、基本語法

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

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

• start with condition1 是用來限制第一層的數據，或者叫根節點數據；以這部分數據為基礎來查找第二層數據，然后以第二層數據查找第三層數據以此類推。

• connect by [prior] id=parentid 這部分是用來指明oracle在查找數據時以怎樣的一種關系去查找；比如說查找第二層的數據時用第一層數據的id去跟表里面記錄的parentid字段進行匹配，如果這個條件成立那么查找出來的數據就是第二層數據，同理查找第三層第四層…等等都是按這樣去匹配。

prior還有一種用法：

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

• 這種用法就表示從下往上查找數據，可以理解為從葉子節點往上查找父級幾點，用第一層數據的parentid去跟表記錄里面的id進行匹配，匹配成功那么查找出來的就是第二層數據；上面的那種就是從父級節點往下查找葉子節點。

其他特性

1. level關鍵字，代表樹形結構中的層級編號；第一層是數字1，第二層數字2，依次遞增。

2. CONNECT_BY_ROOT方法，能夠獲取第一層集結點結果集中的任意字段的值；例CONNECT_BY_ROOT（字段名）。

2、下面來貼兩個例子

2.1 從根節點查找葉子節點

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

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

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

3、寫幾個平常用到的其他一些用法

3.1 生成數字序列結果集

• 使用rownum實現1到10的序列。

select rownum from dual connect by rownum<=10;	

• 使用level實現1到10的序列。

select level from dual connect by level<=10;	

3.3 字符串分割，由一行變為多行

1. 比如說分割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', '[^#]+');		

5、下面再看看幾個例子，針對多條結果集當省略prior關鍵字時怎樣獲得正確的返回結果

5.1 有下面一個結果集 

想要實現1-5,20-30的數據遞增返回1、2、3、4、5、20、21、22、23、24、25、26、27、28、29、30總共16條記錄。

SQL如下：

with temp0 as ( select t.range_num,
          REGEXP_SUBSTR(t.range_num, '[^-]+', 1, 1) minNum, --最小num
          REGEXP_SUBSTR(t.range_num, '[^-]+', 1, 2) maxNum --最大num from range_table t
) select t1.range_num ,t2.lv from temp0 t1 join ( select level lv from dual CONNECT BY LEVEL <= (select max(maxNum) from temp0 )
          ) t2 on (t2.lv >=t1.minNum and t2.lv <=t1.maxNum);

 
上面的sql中是先求出range_num的最大值與最小值，然后利用connect by 特性生成數值序列結果集，最后讓兩個結果集關聯得到需要的結果。

5.2 再看稍微復雜的結果集，輸出結果格式跟上面一樣

SQL如下：

with temp0 as ( select b.range_num,
         REGEXP_SUBSTR(b.range_num, '[^,]+', 1, c.lv) as newport,
         REGEXP_SUBSTR(REGEXP_SUBSTR(b.range_num, '[^,]+', 1, c.lv), '[^-]+', 1, 1) as minNum,
         REGEXP_SUBSTR(REGEXP_SUBSTR(b.range_num, '[^,]+', 1, c.lv), '[^-]+', 1, 2) as maxNum from (select regexp_count(a.range_num, '[^,]+') AS cnt, 
               range_num from range_table a) b join (select LEVEL lv from dual CONNECT BY LEVEL <= 50) c 
  --這里的50表示的是range_num通過，分割后的數量,這里寫死了50也可以sql動態max出來 on c.lv <= b.cnt
) select t1.range_num,t2.lv from temp0 t1 join ( select level lv from dual CONNECT BY LEVEL <= ( select max(to_number(maxNum)) from temp0 
         )
       ) t2 on ((t2.lv >=t1.minNum and t2.lv <=t1.maxNum));

感謝你能夠認真閱讀完這篇文章，希望小編分享的“oracle中connect by怎么用”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!