从由一对连接的站点组成的数据中选择查找每个站的终端站。

Question

我有由一对连接的站点组成的数据(从a到b)，例如，但是只有列a、b：

a b    route stop_no
O U    1     1
A B    2     1
B C    2     2
X Y    3     1
C D    2     3

如例所示，

1. 所有路线都是单向路线，从一个车站只能到达一个终端站。在一条路线内的
2. 站是按顺序排列的(如果你能过滤掉一条路线，stop_no将总是被排序)，但是路线之间是相互混合的(一条路线的两个站可以在其他路线的两个站之间)。

是否有一种方式来写SELECT来获得这样的结果，其中第三列代表终端站？

a b end_station
O U U
A B D
X Y Y
B C D
C D D

如果没有编写“简单”选择的方法，有什么解决方案吗？我需要在INSERT INSERT表存储过程中使用这样的alghoritm。我使用20。

谢谢。

Answer 1

简单的层次化(connect by)查询可以做到这一点：

select  connect_by_root(a) as a, connect_by_root(b) as b, b as end_station
from    routes_table
where   connect_by_isleaf = 1
connect by a = prior b
;

我们只选择叶行(connect_by_isleaf = 1) for each starting row. The output shows the aandb from the "root" row (connect_by_root) and the b‘value )。

Answer 2

使用简单的递归和查询

WITH  rec (end_id, a,b, lvl) AS (
    SELECT  b as end_id,
            a, b,
            0 AS lvl
    FROM tab where b in (select b from tab minus select a from tab)
    UNION ALL 
    SELECT  rec.end_id end_id, 
            tab.a, tab.b,
            lvl + 1
    FROM tab 
    JOIN rec
    ON tab.b  = rec.a
)
 
SELECT 
    a,b, end_id, lvl
FROM rec 
;


A B END_ID     LVL
- - - ----------
O U U          0
X Y Y          0
C D D          0
B C D          1
A B D          2

从botton开始(你的终点)。你让他们

(select b from tab minus select a from tab)

然后递归返回，同时保持end_id (也就是终端站)点。

level显示b和end_id之间的距离。

或者使用相同方法(和结果)的以前的Oracle层次查询

select a, b, connect_by_root(b) as end_id
from tab
start with b in (select b from tab minus select a from tab)
connect by b = prior a
;

A B END_ID
- - ------
C D D
B C D
A B D
O U U
X Y Y