connect by中的條件就表示了父子之間的連接關(guān)系 比如 connect by id=prior pid,但如果connect by中的條件沒有表示記錄之間的父子關(guān)系那會出現(xiàn)什么情況？
常見的，connect by會在構(gòu)造序列的時候使用
select rownum from dual connect by rownum<xxx
代替早期版本的
select rownum from all_objects where rownum <xxx
我們注意到，dual是一個只有一條記錄的表，如果表有多條記錄，將會怎樣？
下面開始實驗
CREATE TABLE T(ID VARCHAR2(1 BYTE));INSERT INTO T ( ID ) VALUES ( 'A'); INSERT INTO T ( ID ) VALUES ( 'B'); INSERT INTO T ( ID ) VALUES ( 'C'); COMMIT;SQL> select id,level from t connect by level<2;I LEVEL- ----------A 1B 1C 1SQL> select id,level from t connect by level<3;I LEVEL- ----------A 1A 2B 2C 2B 1A 2B 2C 2C 1A 2B 2C 2已選擇12行。SQL> select id,level from t connect by level<4;I LEVEL- ----------A 1A 2A 3B 3C 3B 2A 3B 3C 3C 2A 3B 3C 3B 1A 2A 3B 3C 3B 2A 3B 3C 3C 2A 3B 3C 3C 1A 2A 3B 3C 3B 2A 3B 3C 3C 2A 3B 3C 3已選擇39行。
我們很快可以找到其中的規(guī)律，假設(shè)表中有N條記錄, 則記F(N,l)為select id,level from t connect by level<l 的結(jié)果集數(shù)目
那么，
F(N,1)=N
F(N,l) = F(N,l-1)*N+N
于是可以總結(jié)出
F(N,l)=∑power(N,p), p取值為[1,l)
要解釋，也很容易: 當連接條件不能限制記錄之間的關(guān)系時每一條記錄都可以作為自己或者其他記錄的葉子
如下所示：
A 1
A 2
A 3B 3
C 3
B 2
A 3B 3
C 3
C 2
A 3
B 3
在這里，我們看到的是Oracle采用了深度優(yōu)先的算法
我們接著看一個例子，看看在SQL中通過connect by如何將任意一個整數(shù)（不要太大就行）拆分為若干個power(2,n)的和的方法。
先構(gòu)造測試數(shù)據(jù)：
create table ba(n number);insert into ba select 5*rownum from dual connect by rownum<5;commit;select * from ba;
N
-------
5
10
15
20
一個得出結(jié)果的簡單的SQL為
這里為什么要加distinct？你可以嘗試去掉distinct ，看看結(jié)果與保持distinct有多大差別。
然后我們先來看，如果只對其中的一條記錄進行操作，那么加不加distinct，結(jié)果是否是一樣的？比如我們只看第一條記錄5的拆分結(jié)果
select distinct a.n , level, bitand(a.n,power(2,level-1)) from (select * from ba where rownum=1) a connect by level<=floor(log(2,n)+1);
結(jié)果為：
去掉distinct的sql為
輸出結(jié)果，自己運行一下看看。然后你就該思考了，為什么你看到的結(jié)果會是這樣？？？
這里不做過多解釋，做完上面的實驗，然后結(jié)合1樓中所說的，我想你應該就能明白了。
事實上我們有更好的辦法來處理：
with a as (select n, floor(log(2,n)+1) lc from ba)select a.n, bitand(a.n,power(2,b.rn-1)) from a, (select rownum rn from (select max(lc) mlc from a) connect by level<=mlc)bwhere rn<=a.lcorder by 1,2
內(nèi)層SQL先取得所有記錄中可拆分出來的power(2,n)中的n最大可能是多少，然后由此構(gòu)造出序列，最后再做一次關(guān)聯(lián)查詢，
用限制條件rn<=a.lc限制住每個N中可拆分出來的power(2,n)中的n的最大值，由此可以高效得出結(jié)果。
上例實質(zhì)上與 對多記錄按各自指定次數(shù)重復 的性質(zhì)是一樣的。
簡單總結(jié)：
對單記錄/單條數(shù)據(jù)使用connect by，沒問題
但對多條記錄使用connect by，就會碰到問題，千萬要注意。
elect rownum, level, sys_connect_by_path(id, ',') path, id, connect_by_isleaf isleaf from tconnect by nocycle level < 2order by rownum, level, path;select rownum, level, sys_connect_by_path(id, ',') path, id, connect_by_isleaf isleaf from tconnect by nocycle level < 3order by rownum, level, path;select rownum, level, sys_connect_by_path(id, ',') path, id, connect_by_isleaf isleaf from tconnect by nocycle level < 4order by rownum, level, path;下面是執(zhí)行結(jié)果：CHENCH@orcl> select rownum, 2 level, 3 sys_connect_by_path(id, ',') path, 4 id, 5 connect_by_isleaf isleaf 6 from t 7 connect by nocycle level < 2 8 order by rownum, level, path; ROWNUM LEVEL PATH ID ISLEAF---------- ---------- ---------- -- ---------- 1 1 ,A A 1 2 1 ,B B 1 3 1 ,C C 1Elapsed: 00:00:00.01CHENCH@orcl>CHENCH@orcl> select rownum, 2 level, 3 sys_connect_by_path(id, ',') path, 4 id, 5 connect_by_isleaf isleaf 6 from t 7 connect by nocycle level < 3 8 order by rownum, level, path; ROWNUM LEVEL PATH ID ISLEAF---------- ---------- ---------- -- ---------- 1 1 ,A A 0 2 2 ,A,A A 1 3 2 ,A,B B 1 4 2 ,A,C C 1 5 1 ,B B 0 6 2 ,B,A A 1 7 2 ,B,B B 1 8 2 ,B,C C 1 9 1 ,C C 0 10 2 ,C,A A 1 11 2 ,C,B B 1 ROWNUM LEVEL PATH ID ISLEAF---------- ---------- ---------- -- ---------- 12 2 ,C,C C 112 rows selected.Elapsed: 00:00:00.01CHENCH@orcl>CHENCH@orcl> select rownum, 2 level, 3 sys_connect_by_path(id, ',') path, 4 id, 5 connect_by_isleaf isleaf 6 from t 7 connect by nocycle level < 4 8 order by rownum, level, path; ROWNUM LEVEL PATH ID ISLEAF---------- ---------- ---------- -- ---------- 1 1 ,A A 0 2 2 ,A,A A 0 3 3 ,A,A,A A 1 4 3 ,A,A,B B 1 5 3 ,A,A,C C 1 6 2 ,A,B B 0 7 3 ,A,B,A A 1 8 3 ,A,B,B B 1 9 3 ,A,B,C C 1 10 2 ,A,C C 0 11 3 ,A,C,A A 1 ROWNUM LEVEL PATH ID ISLEAF---------- ---------- ---------- -- ---------- 12 3 ,A,C,B B 1 13 3 ,A,C,C C 1 14 1 ,B B 0 15 2 ,B,A A 0 16 3 ,B,A,A A 1 17 3 ,B,A,B B 1 18 3 ,B,A,C C 1 19 2 ,B,B B 0 20 3 ,B,B,A A 1 21 3 ,B,B,B B 1 22 3 ,B,B,C C 1 ROWNUM LEVEL PATH ID ISLEAF---------- ---------- ---------- -- ---------- 23 2 ,B,C C 0 24 3 ,B,C,A A 1 25 3 ,B,C,B B 1 26 3 ,B,C,C C 1 27 1 ,C C 0 28 2 ,C,A A 0 29 3 ,C,A,A A 1 30 3 ,C,A,B B 1 31 3 ,C,A,C C 1 32 2 ,C,B B 0 33 3 ,C,B,A A 1 ROWNUM LEVEL PATH ID ISLEAF---------- ---------- ---------- -- ---------- 34 3 ,C,B,B B 1 35 3 ,C,B,C C 1 36 2 ,C,C C 0 37 3 ,C,C,A A 1 38 3 ,C,C,B B 1 39 3 ,C,C,C C 139 rows selected.Elapsed: 00:00:00.04
關(guān)鍵是 connect by 后面不管是level 還是 rownum都是在查詢結(jié)果集內(nèi)作的限制
SQL> with t as ( 2 select 1 from dual 3 union all 4 select 2 from dual 5 union all 6 select 3 from dual) 7 select * from t connect by rownum < 5; 1---------- 1 1 1 1 2 3已選擇6行。SQL> with t as (select 1 from dual) 2 select * from t connect by rownum < 5; 1---------- 1 1 1 1已選擇4行。
with a as ( select 5 as n from dual union all select 10 from dual union all select 15 from dual union all select 20 from dual )select distinct a.n , level, bitand(a.n,power(2,level-1)) from a connect by level<=floor(log(2,n)+1);SQL> with a as ( 2 select 5 as n from dual 3 union all 4 select 10 from dual 5 union all 6 select 15 from dual 7 union all 8 select 20 from dual 9 ) 10 select distinct a.n , level, bitand(a.n,power(2,level-1)) from a connect by level<=floor(log(2,n)+1); N LEVEL BITAND(A.N,POWER(2,LEVEL-1))---------- ---------- ---------------------------- 10 4 8 15 2 2 20 2 0 20 4 0 15 3 4 5 1 1 10 3 0 10 1 0 20 3 4 5 2 0 20 5 16 10 2 2 5 3 4 15 4 8 15 1 1 20 1 0已選擇16行。
SQL> with a as ( 2 select 5 as n from dual 3 union all 4 select 10 from dual 5 union all 6 select 15 from dual 7 union all 8 select 20 from dual 9 ) 10 select distinct a.n , level, bitand(a.n,power(2,level-1)) from (select * from a where rownum=1) a connect by level<=floor(log(2,n)+1); N LEVEL BITAND(A.N,POWER(2,LEVEL-1))---------- ---------- ---------------------------- 5 1 1 5 2 0 5 3 4已選擇3行。SQL> with a as ( 2 select 5 as n from dual 3 union all 4 select 10 from dual 5 union all 6 select 15 from dual 7 union all 8 select 20 from dual 9 ) 10 select a.n , level, bitand(a.n,power(2,level-1)) from (select * from a where rownum=1) a connect by level<=floor(log(2,n)+1); N LEVEL BITAND(A.N,POWER(2,LEVEL-1))---------- ---------- ---------------------------- 5 1 1 5 2 0 5 3 4已選擇3行。
SQL> with a as ( 2 select 5 as n from dual 3 union all 4 select 10 from dual 5 union all 6 select 15 from dual 7 union all 8 select 20 from dual 9 ) 10 select * from a where a.n=5 connect by rownum<6; N---------- 5 5 5 5 5
SQL> with a as ( 2 select 5 as n from dual 3 union all 4 select 10 from dual 5 union all 6 select 15 from dual 7 union all 8 select 20 from dual 9 ) 10 select * from a where rownum=1 connect by rownum<6;ERROR:ORA-30009: CONNECT BY 操作內(nèi)存不足
with a as ( select 5 as n from dual union all select 10 from dual union all select 15 from dual union all select 20 from dual ) select * from a where a.n=5 connect by level<6;
5555555555已選擇341行。
