有一个表
A B
001 003
003 007
007 004
004 017
017 013
013 024
024 031
031 021
........
怎么通过设置A的起始和截止的值,获取中间的A值的集合?
(比如如果参数是003和004,结果集就是003,007,004;
如果参数是003和013,结果集就是003,007,004,017,013;
如果参数是003和012,结果集就是空,因为003-012不能通过中间集联系上)
这需求能用树状查询实现吗?
------解决思路----------------------
with t as
(select '001' A, '003' B
from dual
union all
select '003' A, '007' B
from dual
union all
select '007' A, '004' B
from dual
union all
select '004' A, '017' B
from dual
union all
select '017' A, '013' B
from dual
union all
select '013' A, '024' B
from dual
union all
select '024' A, '031' B
from dual
union all
select '031' A, '021' B
from dual)
SELECT *
FROM (select LTRIM(SYS_CONNECT_BY_PATH(A, ',')
------解决思路----------------------
','
------解决思路----------------------
B, ',') STR, T.B
from t
start with A = '003'
connect by A = PRIOR B) T1
WHERE T1.B = '013';
------解决思路----------------------
正向反向分别构建树就可以把多余的数据都剔除了,如果怕存在环路可以把connect by 改为connect by nocycle
SELECT * FROM
(select * from T
START WITH A=P_START
CONNECT BY PRIOR B=A)
START WITH B=P_END
CONNECT BY PRIOR A=B