有两张表
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

解决方案 »

  1.   

    当然不一样了。你两个查询的都以A为开头。但是第一个的a的子为c,第二个的a 的子为b
      

  2.   

    你测了是一样的吗?为什么我的是这样的结果?
    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
      

  3.   

    结果一样,只是顺序不同。只须排序即可。16:27:31 scott@ORA1> select t1.*,level,connect_by_iscycle,connect_by_isleaf
    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行。
      

  4.   


    有一丁点不同,第一个查询值为b a,level为2的那行..不是leaf,第二个查询就是leaf了.
      

  5.   

    贴下我的结果SQL> select * from test1;COL1  COL2
    ----- -----
    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.
      

  6.   

    单看col1,col2经过order排序是一样了,但是level不一样的。所以严格意义还是不一样的。因为第一个a的直系儿子是c,第二个a的直系儿子是b
    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
      

  7.   

    看错了,6楼两次查询的isleaf不一样
    但我两次查的结果iscycle和isleaf都不一样,奇怪