对于connect by,现在大多数人已经很熟悉了
connect by中的条件就表示了父子之间的连接关系
比如 connect by id=prior pid
但如果connect by中的条件没有表示记录之间的父子关系(如 connect by里没有 id=prior pid)
那会出现什么情况?
常见的,connect by会在构造序列的时候使用。
select rownum from dual connect by rownum<xxx;
用来代替早期版本(中使用)的下列SQL
select rownum fromall_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.
当level<2时,会有什么样的结果呢?如下:
SQL> select id,level from aaa connect by level<2;
I LEVEL
-----------
a 1
b 1
c 1
当level<3时,又会有什么样的结果呢?如下:
SQL> select id,level from aaa connectby level<3;
I LEVEL
-----------
a 1 <---此处开始第一个子叶树分支
a 2
b 2
c 2
b1 <---此处开始第二个子叶树分支
a 2
b 2
c 2
c 1 <---此处开始第三个子叶树分支
a 2
b 2
c 2
12 rowsselected.
当level<4时,结果如下:
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 rowsselected.
下图为level<4,即
select id,level from aaa connect by level<4;
递归查询到的树状结构:
由上图,可以得出规律如下:
N+N²+。。。。。+N的LEVEL次方
其中,N表示表中有N条记录,LEVEL表示上述树状图中的树的层数,也就是指connect by 子句中的level伪列(或是rownum伪列)值。
树每增加一层,则N+N²+。。。。。+N的LEVEL+1次方=N+N*(N+N²+。。。。。+N的LEVEL次方)。
于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l),即level=1时,power(3,1)=3level=2时,power(3,2)=9,即12-3,level=3时,power(3,3)=27,即39-12。
从而得出如下结论:
假设表中有N条记录,则记F(N,l)为selectid,level from t connect by level<l 的结果集数目。那么:
F(N,1)=N
F(N,l) =F(N,l-1)*N+N
注释:
当连接条件(connect by条件)没有限制记录之间的关系(即 connect by里没有类似 id=prior pid的条件,而是 connect by rownum<xxx 或connect by level<xxx )时,每一条记录都会作为自己或者其他记录的子节点,也就说,每一条记录的子节点就是表上所有的记录。而树的层数就是rownum(或是level)值。
这就是Oracle采用了深度优先的算法。
另外见:
《Oracle层级查询语句(hierarchical query)connectby 用法详解》
参考:
百度 connect by rownum