[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
--//不知道还有好方法,放弃!!
