connect by语法探究

文章来自：http://wenku.baidu.com/link?url=bwBqaaarnesW8toU1aWC1QRO_1Fv_rjMFJ5MnpxdAb3FdKOh8h6xiEFSueTICJuFOsrhrlBosOLn91PBOzYekj1YpzC2antKRbrZBeHN-i3

 

connect by是sql语句中常用的语法，其中的条件就表示了父子之间的连接关系，比如 connect by id=prior pid。常见的，connect by会在构造序列的时候使用，用select rownum from dual connect by rownum<xxx 代替早期版本的 select rownum from all_objects where rownum <xxx。我们注意到，dual是一个只有一条记录的表，如果表有多条记录，将会怎样？动手看一下。

SQL> create table aaa(id varchar2(1));

 

Table created.

 

SQL> insert into aaa (id) values('a');

 

1 row created.

 

SQL> insert into aaa (id) values('b');

 

1 row created.

 

SQL> insert into aaa (id) values('c');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select id,level from aaa connectby level<2;

 

I      LEVEL

- ----------

a          1

b          1

c          1

 

SQL> select id,level from aaa connectby 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 rows selected.

 

SQL> select id,level from aaa connectby 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 rows selected.

规律就是，假设表中有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)，即level=1时，power(3,1)=3  level=2时，power(3,2)=9，即12-3，level=3时，power(3,3)=27，即39-12

说明当连接条件不能限制记录之间的关系时，每一条记录都可以作为自己或者其他记录的叶子。这就是Oracle采用了深度优先的算法。