[20180415]如何取出这几行数据.txt

--//链接http://www.itpub.net/thread-2101289-1-1.html的讨论,测试看看.

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.

--//要求输出:
ID   A
--- ---
  1  11
  1  12
  3  11
  3  15
  3  16

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  and prior a <> a start with a=11;
    A_ROOT     ISLEAF    ISCYCLE         ID          A
---------- ---------- ---------- ---------- ----------
        11          0          0          1         11
        11          1          1          1         12
        11          0          0          3         11
        11          0          1          3         15
        11          1          1          3         16
        11          0          1          3         16
        11          1          1          3         15
7 rows selected.

--//可以发现还是不能满足需求.
SELECT CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, level,lpad(' ', level*2,' ')||a as c20, t.*
   FROM t
connect by nocycle prior id =  id
    AND prior a <> a
  start WITH a =  11;

    A_ROOT     ISLEAF    ISCYCLE      LEVEL C20                          ID          A
---------- ---------- ---------- ---------- -------------------- ---------- ----------
        11          0          0          1   11                          1         11
        11          1          1          2     12                        1         12
        11          0          0          1   11                          3         11
        11          0          1          2     15                        3         15
        11          1          1          3       16                      3         16
        11          0          1          2     16                        3         16
        11          1          1          3       15                      3         15
7 rows selected.

--//如何避免后面2行的输出.., 也就是如何避免从2层开始再探查.
SELECT CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle, level,lpad(' ', level*2,' ')||a as c20, t.*
   FROM t
connect by nocycle prior id =  id
    AND prior a < a
  start WITH a =  11;

    A_ROOT     ISLEAF    ISCYCLE      LEVEL C20                          ID          A
---------- ---------- ---------- ---------- -------------------- ---------- ----------
        11          0          0          1   11                          1         11
        11          1          0          2     12                        1         12
        11          0          0          1   11                          3         11
        11          0          0          2     15                        3         15
        11          1          0          3       16                      3         16
        11          1          0          2     16                        3         16

6 rows selected.

--//还是不行,放弃!!简单一点加distinct.

SCOTT@book> select distinct id,a from (select CONNECT_BY_ROOT(a) a_root, CONNECT_BY_ISLEAF isleaf, CONNECT_BY_ISCYCLE iscycle,level, t.* from t  connect by  nocycle prior id=  id  and prior a <> a start with a=11) order by 1,2;
        ID          A
---------- ----------
         1         11
         1         12
         3         11
         3         15
         3         16

--//不知道还有好方法,放弃!!