選擇介於 FROM 和 TO 之間的道路

November 3, 2017

我有一張road桌子：

create table road
  (road_id int, 
   name_ nvarchar2(15), 
   from_ nvarchar2(15), 
   to_ nvarchar2(15), 
   sort int
  );
insert into road (road_id,name_,from_,to_,sort) values (4,'Street A','Street 1','Street 2','1');
insert into road (road_id,name_,from_,to_,sort) values (6,'Street A','Street 2','Street 3','2');
insert into road (road_id,name_,from_,to_,sort) values (5,'Street A','Street 3','Street 4','3');
insert into road (road_id,name_,from_,to_,sort) values (3,'Street B','Street VI','Street VII','600');
insert into road (road_id,name_,from_,to_,sort) values (7,'Street B','Street VII','Street VIII','700');
insert into road (road_id,name_,from_,to_,sort) values (1,'Street B','Street VIII','Street IX','800');
insert into road (road_id,name_,from_,to_,sort) values (2,'Street B','Street I','Street II','100');
insert into road (road_id,name_,from_,to_,sort) values (9,'Street B','Street II','Street III','200');
insert into road (road_id,name_,from_,to_,sort) values (11,'Street B','Street III','Street IV','300');
insert into road (road_id,name_,from_,to_,sort) values (8,'Street C','Street X','Street Y','9');
insert into road (road_id,name_,from_,to_,sort) values (10,'Street C','Street Y','Street Z','10');
commit;
select * from road order by name_, sort;

  ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
        4 Street A        Street 1        Street 2                 1
        6 Street A        Street 2        Street 3                 2
        5 Street A        Street 3        Street 4                 3

        2 Street B        Street I        Street II              100
        9 Street B        Street II       Street III             200
       11 Street B        Street III      Street IV              300

   {Gap in Street B}

        3 Street B        Street VI       Street VII             600
        7 Street B        Street VII      Street VIII            700
        1 Street B        Street VIII     Street IX              800

        8 Street C        Street X        Street Y                 9
       10 Street C        Street Y        Street Z                10

最後，我想製作一個工具from_，允許使用者在 a和 a之間選擇道路to_（值將輸入參數中）。

為此，我認為我需要進行一個查詢，該查詢可以執行以下操作：

選擇位於Street II和Street VIII（含）之間name_的Street B的道路。

  ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
        9 Street B        Street II       Street III             200
       11 Street B        Street III      Street IV              300
        3 Street B        Street VI       Street VII             600
        7 Street B        Street VII      Street VIII            700

我怎樣才能做到這一點？

select *
from road
connect by (prior to_ = from_ and from_ != 'Street V')
start with name_ = 'Street B' and from_ = 'Street II'
;

  ROAD_ID NAME_      FROM_      TO_              SORT
---------- ---------- ---------- ---------- ----------
        5 Street B   Street II  Street III        200
        6 Street B   Street III Street IV         300
        7 Street B   Street IV  Street V          400

回答更新的問題：

select road_id, name_, from_, to_, sort
from
(
 select road_id, name_, from_, to_, sort, 
 row_number() over (order by sort) as rn from road
 where name_ = 'Street B'
)
connect by (prior rn + 1 = rn and from_ != 'Street VIII')
start with from_ = 'Street II'
;

  ROAD_ID NAME_           FROM_           TO_                   SORT
---------- --------------- --------------- --------------- ----------
        9 Street B        Street II       Street III             200
       11 Street B        Street III      Street IV              300
        3 Street B        Street VI       Street VII             600
        7 Street B        Street VII      Street VIII            700