[20180415]如何取出這幾行資料.txt

[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

--//不知道還有好方法,放棄!!