[20180416]connect by和nocycle.txt
--//链接http://www.itpub.net/thread-2101289-1-1.html的讨论,感觉在使用nocycle有点困惑,做一个记录:
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t (id number ,a number);
Table created.
insert into t values (1,11);
insert into t values (1,12);
insert into t values (2,12);
insert into t values (3,11);
insert into t values (3,15);
insert into t values (3,16);
commit ;
SCOTT@book> select * from t;
ID A
--- ---
1 11
1 12
2 12
3 11
3 15
3 16
6 rows selected.
2.测试:
SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t connect by nocycle prior id= id start with a=11;
A_ROOT ISLEAF ISCYCLE ID A
------------ ------------ ------------ ------------ ------------
11 1 1 1 11
11 1 1 3 11
--//开始自己非常不理解为什么仅仅2行输出.实际上nocycle就指示出现cycle的不输出.
--//当执行prior id= id时,取第一行id=1,A=11时就已经形成环路.因为 prior id= id.
--//这样id=1,A=12的行永远不会输出.
--//如果写成如下:prior id= id and prior a < a
SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t connect by nocycle prior id= id and prior a < a start with a=11;
A_ROOT ISLEAF ISCYCLE ID A
------------ ------------ ------------ ------------ ------------
11 0 0 1 11
11 1 0 1 12
11 0 0 3 11
11 0 0 3 15
11 1 0 3 16
11 1 0 3 16
6 rows selected.
--//当取第一行id=1,A=11时,这个prior id= id and prior a < a 就是假,没有形成环路,这样可以继续扫描.
--//而且这样理论也不存在环路.
SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t connect by prior id= id and prior a < a start with a=11;
select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, t.* from t connect by prior id= id and prior a < a start with a=11
*
ERROR at line 1:
ORA-30930: NOCYCLE keyword is required with CONNECT_BY_ISCYCLE pseudocolumn
--//这里是因为使用CONNECT_BY_ISCYCLE,取消这个查询写成如下:
SCOTT@book> select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, t.* from t connect by prior id= id and prior a < a start with a=11;
A_ROOT ISLEAF ID A
------------ ------------ ------------ ------------
11 0 1 11
11 1 1 12
11 0 3 11
11 0 3 15
11 1 3 16
11 1 3 16
6 rows selected.
--//当然我的查询还是不能满足作者的需求..^_^.
