Oracle SQL使用以前的列值来查找下一行

What I currently have:

我现在有什么：

ID  FROM_REF TO_REF
--- -------- ----
1   1        10
1   2        3
1   3        4
1   3        5
1   5        6
1   6        7
1   7        9
1   8        11
1   9        8
1   10       2

What's needed is the SORT column which I can use to sort according later on:

我需要的是SORT列，我可以使用它来进行排序：

ID  FROM_REF TO_REF SORT
--- -------- ----   ----
1   1        10     1
1   10       2      2
1   2        3      3
1   3        4      4
1   4        5      5
1   5        6      6
1   6        7      7
1   7        9      8
1   9        8      9
1   8        11     10

NOTE: TO_REF column indicate next FROM_REF.

注意：TO_REF列表示下一个FROM_REF。

How do I write SQL to achieve the SORT column as result?

如何编写SQL以实现SORT列的结果？

Please help.

请帮忙。

2 个解决方案

#1

A simple hierarchical query, I presume.

我推测，这是一个简单的分层查询。

SQL> with test (from_ref, to_ref) as
  2  (select 1, 10 from dual union
  3   select 2, 3  from dual union
  4   select 3, 4  from dual union
  5   select 4, 5  from dual union
  6   select 5, 6  from dual union
  7   select 6, 7  from dual union
  8   select 7, 9  from dual union
  9   select 8, 11 from dual union
 10   select 9, 8  from dual union
 11   select 10, 2 from dual
 12  )
 13  select from_ref, to_ref, level rn
 14  from test
 15  connect by  from_ref = prior to_ref
 16  start with from_ref = (select min(from_ref) from test);

  FROM_REF     TO_REF         RN
---------- ---------- ----------
         1         10          1
        10          2          2
         2          3          3
         3          4          4
         4          5          5
         5          6          6
         6          7          7
         7          9          8
         9          8          9
         8         11         10

10 rows selected.

SQL>

#2

You can use a RECURSIVE function.

您可以使用RECURSIVE功能。

WITH X (ID, FROM_REF, TO_REF) AS
(
    SELECT ID, FROM_REF, TO_REF
    FROM   tbl 
    WHERE  FROM_REF = 1
   UNION ALL
    SELECT tbl.ID, tbl.FROM_REF, tbl.TO_REF
    FROM   tbl
    JOIN   X
    ON     tbl.ID = X.ID
    AND    tbl.FROM_REF = X.TO_REF
)
SELECT ID, FROM_REF, TO_REF
FROM   X

ID | FROM_REF | TO_REF
-: | -------: | -----:
 1 |        1 |     10
 1 |       10 |      2
 1 |        2 |      3
 1 |        3 |      4
 1 |        4 |      5
 1 |        5 |      6
 1 |        6 |      7
 1 |        7 |      9
 1 |        9 |      8
 1 |        8 |     11

dbfiddle here

dbfiddle在这里

#1