有两张表
test1
col1 | col2
a c
c b
a b
b atest2
col1 | col2
a b
b a
a c
c b两张表只是记录行的顺序不同,但下面查询的结果却不一样,为什么?
select t1.*,level,connect_by_iscycle,connect_by_isleaf
from test1 t1
start with col1='a'
connect by nocycle prior col2=col1
;select t1.*,level,connect_by_iscycle,connect_by_isleaf
from test2 t1
start with col1='a'
connect by nocycle prior col2=col1
test1
col1 | col2
a c
c b
a b
b atest2
col1 | col2
a b
b a
a c
c b两张表只是记录行的顺序不同,但下面查询的结果却不一样,为什么?
select t1.*,level,connect_by_iscycle,connect_by_isleaf
from test1 t1
start with col1='a'
connect by nocycle prior col2=col1
;select t1.*,level,connect_by_iscycle,connect_by_isleaf
from test2 t1
start with col1='a'
connect by nocycle prior col2=col1
解决方案 »
- 一个Oracle分区表问题
- 怎样优化此sql
- 求 Oracle database 10g sql 开发指南的电子版
- oci ofetch 空的clob时出错ORA-01279: db_files too large,高人指点
- 怎么用sqlldr把指定目录下的所有多个文件导入数据库?目录确定,但文件名及文件个数不定?
- 论坛数据库设计
- 谁知道怎么对象嵌套表的使用呀?
- 更改表結構以後,如何查詢到該表對應的所有的存儲過程,觸發器,視圖等,並重新編譯?
- 视图问题
- 求救:获取日期字段为空的记录信息(在线等待)
- windows xp 上装个oracle8i客户端老是报错
- powerdesigner12.5生成sql,表名带有“”
que1:
1 a c 1 0 0
2 c b 2 0 0
3 b a 3 1 1
4 a b 1 0 0
5 b a 2 0 0
6 a c 3 1 1
que2:
1 a b 1 0 0
2 b a 2 1 1
3 a c 3 1 1
4 a c 1 0 0
5 c b 2 0 0
6 b a 3 1 1
16:27:32 2 from test1 t1
16:27:32 3 start with col1='a'
16:27:32 4 connect by nocycle prior col2=col1
16:27:32 5 ;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a c 1 0 0
c b 2 0 0
b a 3 1 1
a b 1 0 0
b a 2 1 0
a c 3 1 1已选择6行。16:27:33 scott@ORA1> select t1.*,level,connect_by_iscycle,connect_by_isleaf
16:27:42 2 from test2 t1
16:27:42 3 start with col1='a'
16:27:42 4 connect by nocycle prior col2=col1
16:27:42 5 ;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a b 1 0 0
b a 2 1 1
a c 3 1 1
a c 1 0 0
c b 2 0 0
b a 3 1 1已选择6行。16:27:43 scott@ORA1> select *
16:28:17 2 from
16:28:17 3 (
16:28:17 4 select t1.*,level,connect_by_iscycle,connect_by_isleaf
16:28:17 5 from test2 t1
16:28:17 6 start with col1='a'
16:28:17 7 connect by nocycle prior col2=col1
16:28:17 8 )
16:28:17 9 order by col1, col2;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a b 1 0 0
a c 3 1 1
a c 1 0 0
b a 3 1 1
b a 2 1 1
c b 2 0 0已选择6行。16:28:18 scott@ORA1> select *
16:28:26 2 from
16:28:26 3 (
16:28:26 4 select t1.*,level,connect_by_iscycle,connect_by_isleaf
16:28:26 5 from test1 t1
16:28:26 6 start with col1='a'
16:28:26 7 connect by nocycle prior col2=col1
16:28:26 8 )
16:28:26 9 order by col1, col2;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a b 1 0 0
a c 1 0 0
a c 3 1 1
b a 3 1 1
b a 2 1 0
c b 2 0 0已选择6行。
有一丁点不同,第一个查询值为b a,level为2的那行..不是leaf,第二个查询就是leaf了.
----- -----
a c
c b
a b
b aSQL> select * from test2;COL1 COL2
----- -----
a b
b a
a c
c bSQL> select t1.*,level,connect_by_iscycle,connect_by_isleaf
2 from test1 t1
3 start with col1='a'
4 connect by nocycle prior col2=col1
5 ; COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
----- ----- ---------- ------------------ -----------------
a c 1 0 0
c b 2 0 0
b a 3 1 1
a b 1 0 0
b a 2 0 0
a c 3 1 16 rows selected.SQL> select t1.*,level,connect_by_iscycle,connect_by_isleaf
2 from test2 t1
3 start with col1='a'
4 connect by nocycle prior col2=col1;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
----- ----- ---------- ------------------ -----------------
a b 1 0 0
b a 2 1 1
a c 3 1 1
a c 1 0 0
c b 2 0 0
b a 3 1 16 rows selected.
SQL> select t1.*,level,connect_by_iscycle,connect_by_isleaf
2 from test1 t1
3 start with col1='a'
4 connect by nocycle prior col2=col1 ;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a c 1 0 0
c b 2 0 0
b a 3 1 1
a b 1 0 0
b a 2 1 0
a c 3 1 16 rows selectedSQL> select t1.*,level,connect_by_iscycle,connect_by_isleaf
2 from test2 t1
3 start with col1='a'
4 connect by nocycle prior col2=col1 ;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a b 1 0 0
b a 2 1 1
a c 3 1 1
a c 1 0 0
c b 2 0 0
b a 3 1 16 rows selected
但是经过order by排序后仔细观察level是不一样的
SQL> select t1.*,level,connect_by_iscycle,connect_by_isleaf
2 from test1 t1
3 start with col1='a'
4 connect by nocycle prior col2=col1 order by 1,2 ;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a b 1 0 0
a c 1 0 0
a c 3 1 1
b a 3 1 1
b a 2 1 0
c b 2 0 06 rows selectedSQL> select t1.*,level,connect_by_iscycle,connect_by_isleaf
2 from test2 t1
3 start with col1='a'
4 connect by nocycle prior col2=col1 order by 1,2 ;COL1 COL2 LEVEL CONNECT_BY_ISCYCLE CONNECT_BY_ISLEAF
---------- ---------- ---------- ------------------ -----------------
a b 1 0 0
a c 3 1 1
a c 1 0 0
b a 3 1 1
b a 2 1 1
c b 2 0 06 rows selected
但我两次查的结果iscycle和isleaf都不一样,奇怪