1、基本語法

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

2

一般用來查找存在父子關(guān)系的數(shù)據(jù)，也就是樹形結(jié)構(gòu)的數(shù)據(jù)；其返還的數(shù)據(jù)也能夠明確的區(qū)分出每一層的數(shù)據(jù)。

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?這部分是用來指明oracle在查找數(shù)據(jù)時(shí)以怎樣的一種關(guān)系去查找；比如說查找第二層的數(shù)據(jù)時(shí)用第一層數(shù)據(jù)的id去跟表里面記錄的parentid字段進(jìn)行匹配，如果這個(gè)條件成立那么查找出來的數(shù)據(jù)就是第二層數(shù)據(jù)，同理查找第三層第四層…等等都是按這樣去匹配。

prior還有一種用法：

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

2

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

其他特性

level關(guān)鍵字，代表樹形結(jié)構(gòu)中的層級編號；第一層是數(shù)字1，第二層數(shù)字2，依次遞增。

CONNECT_BY_ROOT方法，能夠獲取第一層集結(jié)點(diǎn)結(jié)果集中的任意字段的值；例CONNECT_BY_ROOT(字段名)。

2、下面來貼兩個(gè)例子

2.1 從根節(jié)點(diǎn)查找葉子節(jié)點(diǎn)

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

2

3

4

2.2 從葉子節(jié)點(diǎn)查找上層節(jié)點(diǎn)

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

2

3

4

5

6

7

8

9

10

11

3、寫幾個(gè)平常用到的其他一些用法

3.1 生成數(shù)字序列結(jié)果集

使用rownum實(shí)現(xiàn)1到10的序列。

select rownum from dual connect by rownum<=10;1

結(jié)果集如下:

使用level實(shí)現(xiàn)1到10的序列。

select level from dual connect by level<=10;1

結(jié)果集如下：

3.2 查詢當(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)同樣的效果1

2

3

4

5

6

7

d 表示一星期中的第幾天

iw 表示一年中的第幾周

3.3 字符串分割，由一行變?yōu)槎嘈?/p>

比如說分割01#02#03#04這種有規(guī)律的字符串

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

2

4、省略prior關(guān)鍵字時(shí)數(shù)據(jù)的返回策略

構(gòu)造一個(gè)結(jié)果集，其中包含兩條數(shù)據(jù)；然后查詢level為1,2,3層的數(shù)據(jù)。

select t.*, level from (select 1 as num from dual union select 2 as num from dual

) t connect by level <= 3;1

2

3

4

5

6

從上面截圖的結(jié)果可以看出來省略prior關(guān)鍵字時(shí)第1層的數(shù)據(jù)就是初始結(jié)果集，第2層的數(shù)據(jù)是初始結(jié)果集的兩倍，第3層的數(shù)據(jù)是初始結(jié)果集的3倍；假設(shè)初始結(jié)果集的記錄為n條，查詢m層的記錄，則返回的記錄數(shù)就是：

?條記錄。

在省略prior關(guān)鍵字對數(shù)據(jù)進(jìn)行操作時(shí)需要特別注意，返回的數(shù)據(jù)不一定是你所期望的那樣。

5、下面再看看幾個(gè)例子，針對多條結(jié)果集當(dāng)省略prior關(guān)鍵字時(shí)怎樣獲得正確的返回結(jié)果

5.1 有下面一個(gè)結(jié)果集

想要實(shí)現(xiàn)1-5,20-30的數(shù)據(jù)遞增返回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);1

2

3

4

5

6

7

8

9

10

11

12

13

?

上面的sql中是先求出range_num的最大值與最小值，然后利用connect by 特性生成數(shù)值序列結(jié)果集，最后讓兩個(gè)結(jié)果集關(guān)聯(lián)得到需要的結(jié)果。

5.2 再看稍微復(fù)雜的結(jié)果集，輸出結(jié)果格式跟上面一樣

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通過，分割后的數(shù)量,這里寫死了50也可以sql動態(tài)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));1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

總結(jié)

以上是生活随笔為你收集整理的oracle+connect+by+level,oracle connect by用法篇的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。