深入理解connect by【三年的坑，补完了】

谁

对于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是一个只有一条记录的表，如果表有多条记录，将会怎样？

下面开始实验
环境：windows xp sp2 ＋ Oracle 9208
（10.1版本connect by有问题）

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;

1. SQL> select id,level from t connect by level<2;
   
2. 
   
3. I      LEVEL
   
4. - ----------
   
5. A          1
   
6. B          1
   
7. C          1
   
8. 
   
9. SQL> select id,level from t connect by level<3;
   
10. 
    
11. I      LEVEL
    
12. - ----------
    
13. A          1
    
14. A          2
    
15. B          2
    
16. C          2
    
17. B          1
    
18. A          2
    
19. B          2
    
20. C          2
    
21. C          1
    
22. A          2
    
23. B          2
    
24. C          2
    
25. 
    
26. 已选择12行。
    
27. 
    
28. SQL> select id,level from t connect by level<4;
    
29. 
    
30. I      LEVEL
    
31. - ----------
    
32. A          1
    
33. A          2
    
34. A          3
    
35. B          3
    
36. C          3
    
37. B          2
    
38. A          3
    
39. B          3
    
40. C          3
    
41. C          2
    
42. A          3
    
43. B          3
    
44. C          3
    
45. B          1
    
46. A          2
    
47. A          3
    
48. B          3
    
49. C          3
    
50. B          2
    
51. A          3
    
52. B          3
    
53. C          3
    
54. C          2
    
55. A          3
    
56. B          3
    
57. C          3
    
58. C          1
    
59. A          2
    
60. A          3
    
61. B          3
    
62. C          3
    
63. B          2
    
64. A          3
    
65. B          3
    
66. C          3
    
67. C          2
    
68. A          3
    
69. B          3
    
70. C          3
    
71. 
    
72. 已选择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)的和的方法。

先构造测试数据：

1. create table ba(n number);
   
2. insert into ba select 5*rownum from dual connect by rownum<5;
   
3. commit;
   
4. select * from ba;

复制代码

展示ba中的数据为：
N
-----------------------
5
10
15
20


一个得出结果的简单的SQL为

1. 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的拆分结果

1. 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);

复制代码

结果为：

1. N    LEVEL    BITAND(A.N,POWER(2,LEVEL-1))
   
2. ----------------------------------------------------------------
   
3. 5    1             1
   
4. 5    2             0
   
5. 5    3             4

复制代码

去掉distinct的sql为

1. 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楼中所说的，我想你应该就能明白了。

———————————————————————我是Long Long Ago的大坑的分界线———————————————————————————

事实上我们有更好的办法来处理：

1. with a as (select n, floor(log(2,n)+1) lc from ba)
   
2. select a.n, bitand(a.n,power(2,b.rn-1)) from a,
   
3. (select rownum rn from
   
4.         (select max(lc) mlc from a)
   
5.         connect by level<=mlc
   
6. )b
   
7. where rn<=a.lc
   
8. order by 1,2

复制代码

内层SQL先取得所有记录中可拆分出来的power(2,n)中的n最大可能是多少，然后由此构造出序列，最后再做一次关联查询，用限制条件rn<=a.lc限制住每个N中可拆分出来的power(2,n)中的n的最大值，由此可以高效得出结果。

上例实质上与  对多记录按各自指定次数重复  的性质是一样的。


简单总结：
对单记录/单条数据使用connect by，没问题
但对多条记录使用connect by，就会碰到问题，千万要注意。

[ 本帖最后由 谁 于 2008-7-3 12:47 编辑 ]

谁

再占一个
～～～～～～～～～～～～～～
再度登录之前看了一下自己的信息：

• 用户组注册会员
  

• 在线时间211 小时
• 注册时间2006-10-28 23:57
• 最后访问2010-11-4 20:41
• 上次活动时间2010-11-5 23:17
• 上次发表时间2010-11-1 13:21
• 所在时区(GMT +08:00) 北京, 香港, 帕斯, 新加坡, 台北
  

哇，快1年没登录了，不过我还是常常关注itpub，借此注册五周年之际，我将2楼的帖完成。
其实当时想说的就是当connect by的表中有多条记录时，如何使用connect by正确创造序列。但在没多久之后，其实已经有人给出了与我相同思路的做法，而在我要完成这个很长久很长久的坑的时候，发现已经有更强的帖子出现了——求反GROUP BY的写法。这个帖子聚集了各种各样的做法，向我们展示了要创造序列，不用connect by，也有很多办法可以做到，在此膜拜各位强人

xiaodong_1567

支持原创

zhangweicai74

等待继续:LZ研究这个到深夜?

7854236

顶一个

zhouwf0726

http://www.itpub.net/620427.html

hzwq123

不错！ 这两天我也困惑在这个问题上 也做了些测试 不过LZ的测试比我深入多了

关键是 connect by 后面不管是level 还是 rownum都是在查询结果集内作的限制
比方
Table:
X
--
1
2
3
那么select x from table connect by rownum <5
结果就是
x
---
1
1
1
1
2
3

ilovemk

不错啊，楼主很有钻研精神啊。

阿日

值得我学习,努力