Oracle的Connect By理解
connect by中的条件就表示了父子之间的连接关系 比如 connect by id=prior pid,但如果connect by中的条件没有表示记录之间的父子关系那会出现什么情况？
常见的，connect by会在构造序列的时候使用
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 1 B 1 C 1 SQL> select id,level from t connect by level<3; I LEVEL - ---------- A 1 A 2 B 2 C 2 B 1 A 2 B 2 C 2 C 1 A 2 B 2 C 2 已选择12行。 SQL> select id,level from t connect by level<4; I LEVEL - ---------- A 1 A 2 A 3 B 3 C 3 B 2 A 3 B 3 C 3 C 2 A 3 B 3 C 3 B 1 A 2 A 3 B 3 C 3 B 2 A 3 B 3 C 3 C 2 A 3 B 3 C 3 C 1 A 2 A 3 B 3 C 3 B 2 A 3 B 3 C 3 C 2 A 3 B 3 C 3 已选择39行。
我们很快可以找到其中的规律，假设表中有N条记录, 则记F(N,l)为select id,level from t connect by level<l 的结果集数目
那么，
F(N,1)=N
F(N,l) = F(N,l-1)*N+N
于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)
要解释，也很容易:当连接条件不能限制记录之间的关系时每一条记录都可以作为自己或者其他记录的叶子
如下所示：
A 1
A 2
A 3
B 3
C 3
B 2
A 3
B 3
C 3
C 2
A 3
B 3
C 3
在这里，我们看到的是Oracle采用了深度优先的算法
我们接着看一个例子，看看在SQL中通过connect by如何将任意一个整数（不要太大就行）拆分为若干个power(2,n)的和的方法。
先构造测试数据：
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
一个得出结果的简单的SQL为
- select distinct a.n , level, bitand(a.n,power(2,level-1)) from ba a connect by level<=floor(log(2,n)+1)
这里为什么要加distinct？你可以尝试去掉distinct ，看看结果与保持distinct有多大差别。
然后我们先来看，如果只对其中的一条记录进行操作，那么加不加distinct，结果是否是一样的？比如我们只看第一条记录5的拆分结果
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);
结果为：
- N LEVEL BITAND(A.N,POWER(2,LEVEL-1))
- ----------------------------------------------------------------
- 5 1 1
- 5 2 0
- 5 3 4
去掉distinct的sql为
- select 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);
输出结果，自己运行一下看看。然后你就该思考了，为什么你看到的结果会是这样？？？
这里不做过多解释，做完上面的实验，然后结合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 )b where rn<=a.lc order by 1,2
内层SQL先取得所有记录中可拆分出来的power(2,n)中的n最大可能是多少，然后由此构造出序列，最后再做一次关联查询，
用限制条件rn<=a.lc限制住每个N中可拆分出来的power(2,n)中的n的最大值，由此可以高效得出结果。
上例实质上与 对多记录按各自指定次数重复 的性质是一样的。
简单总结：
对单记录/单条数据使用connect by，没问题
但对多条记录使用connect by，就会碰到问题，千万要注意。
elect rownum, level, sys_connect_by_path(id, ',') path, id, connect_by_isleaf isleaf from t connect by nocycle level < 2 order by rownum, level, path; select rownum, level, sys_connect_by_path(id, ',') path, id, connect_by_isleaf isleaf from t connect by nocycle level < 3 order by rownum, level, path; select rownum, level, sys_connect_by_path(id, ',') path, id, connect_by_isleaf isleaf from t connect by nocycle level < 4 order by rownum, level, path; 下面是执行结果： 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 1 Elapsed: 00:00:00.01 CHENCH@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 1 12 rows selected. Elapsed: 00:00:00.01 CHENCH@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 1 39 rows selected. Elapsed: 00:00:00.04
关键是 connect by 后面不管是level 还是 rownum都是在查询结果集内作的限制
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 操作内存不足
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;
5 5 5 5 5 5 5 5 5 5 已选择341行。
小小菜鸟一枚
