SQL基于2列排序随机行

Any idea how to order this table in Oracle9:

知道如何在Oracle9中订购此表：

START | END | VALUE
  A   |  F  |   1
  D   |  H  |   9
  F   |  C  |   8
  C   |  D  |  12

To make it look like this:

使它看起来像这样：

START | END | VALUE
   A  |  F  |   1
   F  |  C  |  12
   C  |  D  |   8
   D  |  H  |   9

Goal is to start every next row with the end from the previous row

目标是从上一行的结尾开始每一行

1 个解决方案

#1

This cannot be done with the order by clause alone, as it would have to find the record without a predecessor first, then find the next record comparing end and start column of the two records etc. This is an iterative process for which you need a recursive query.

单独使用order by子句不能这样做，因为它必须首先找到没有前任的记录，然后找到比较两个记录的end和start列的下一条记录等。这是一个迭代过程，你需要一个递归查询。

That recursive query would find the first record, then the next and so on, giving them sequence numbers. Then you'd use the result and order by those generated numbers.

该递归查询将找到第一个记录，然后是下一个等等，给它们序列号。然后，您将使用生成的数字的结果和顺序。

Here is how to do it in standard SQL. This is supported from Oracle 11g onwards only, however. In Oracle 9 you'll have to use CONNECT BY with which I am not familiar. Hopefully you or someone else can convert the query for you:

以下是如何在标准SQL中执行此操作。但是，仅从Oracle 11g开始支持此功能。在Oracle 9中，您将不得不使用我不熟悉的CONNECT BY。希望您或其他人可以为您转换查询：

with chain(startkey, endkey, value, pos) as
(
  select startkey, endkey, value, 1 as pos 
  from mytable
  where not exists (select * from mytable prev where prev.endkey = mytable.startkey)
  union all
  select mytable.startkey, mytable.endkey, mytable.value, chain.pos + 1 as pos 
  from chain 
  join mytable on mytable.startkey = chain.endkey
)
select startkey, endkey, value
from chain
order by pos;

UPDATE: As you say the data is cyclic, you'd have to change above query so as to start with an arbitrarily chosen row and stop when through:

更新：如你所说数据是循环的，你必须改变上面的查询，以便从任意选择的行开始，并在通过时停止：

with chain(startkey, endkey, value, pos) as
(
  select startkey, endkey, value, 1 as pos 
  from mytable
  where rownum = 1
  union all
  select mytable.startkey, mytable.endkey, mytable.value, chain.pos + 1 as pos 
  from chain 
  join mytable on mytable.startkey = chain.endkey
)
cycle startkey set cycle to 1 default 0
select startkey, endkey, value
from chain
where cycle = 0
order by pos;

#1