選擇數字範圍不重疊的位置

August 29, 2018

我有兩個表，其中包含有關道路建設活動的記錄：

table_a是主列表。
table_b是一個遺留列表。

對於每條道路，每年，我想從中選擇table_b不存在的記錄table_a。

此外，記錄不應沿道路在空間上重疊。更具體地說，in 中記錄的from_mand不應與in 中的and重疊。to_m``table_b``from_m``to_m``table_a

**我怎樣才能做到這一點？**我沒有 Oracle Spatial。

Excel中的數據（方便查看）：

Excel 中的數據如下所示：

綠色的記錄應該被查詢選中；紅色的記錄不應該。

DDL：

表 A：

 create table table_a 
  (
   id number(4,0), 
   road_id number(4,0), 
   year number(4,0), 
   from_m number(4,0), 
   to_m number(4,0)
  );

insert into table_a (id,road_id,year,from_m,to_m) values (1,1,2000,0,100);
insert into table_a (id,road_id,year,from_m,to_m) values (2,1,2005,0,25);
insert into table_a (id,road_id,year,from_m,to_m) values (3,1,2005,50,75);
insert into table_a (id,road_id,year,from_m,to_m) values (4,1,2005,75,100);
insert into table_a (id,road_id,year,from_m,to_m) values (5,1,2010,10,50);
insert into table_a (id,road_id,year,from_m,to_m) values (6,1,2010,50,90);
insert into table_a (id,road_id,year,from_m,to_m) values (7,1,2015,40,100);
insert into table_a (id,road_id,year,from_m,to_m) values (8,2,2020,0,40);
insert into table_a (id,road_id,year,from_m,to_m) values (9,2,2020,0,40);
insert into table_a (id,road_id,year,from_m,to_m) values (10,3,2025,90,150);
commit;

select * from table_a;

       ID    ROAD_ID       YEAR     FROM_M       TO_M
---------- ---------- ---------- ---------- ----------
        1          1       2000          0        100
        2          1       2005          0         25
        3          1       2005         50         75
        4          1       2005         75        100
        5          1       2010         10         50
        6          1       2010         50         90
        7          1       2015         40        100
        8          2       2020          0         40
        9          2       2020          0         40
       10          3       2025         90        150

表 B：

 create table table_b 
  (
  id number(4,0), 
   road_id number(4,0), 
   year number(4,0), 
   from_m number(4,0), 
   to_m number(4,0)
  );

insert into table_b (id,road_id,year,from_m,to_m) values (1,1,1995,0,100);
insert into table_b (id,road_id,year,from_m,to_m) values (2,1,2001,0,50);
insert into table_b (id,road_id,year,from_m,to_m) values (3,1,2005,20,80);
insert into table_b (id,road_id,year,from_m,to_m) values (4,1,2005,0,100);
insert into table_b (id,road_id,year,from_m,to_m) values (5,1,2010,0,10);
insert into table_b (id,road_id,year,from_m,to_m) values (6,1,2010,90,100);
insert into table_b (id,road_id,year,from_m,to_m) values (7,1,2010,5,85);
insert into table_b (id,road_id,year,from_m,to_m) values (8,1,2015,40,100);
insert into table_b (id,road_id,year,from_m,to_m) values (9,1,2015,0,40);
insert into table_b (id,road_id,year,from_m,to_m) values (10,2,2020,0,41);
insert into table_b (id,road_id,year,from_m,to_m) values (11,3,2025,155,200);
insert into table_b (id,road_id,year,from_m,to_m) values (12,3,2025,199,300);
insert into table_b (id,road_id,year,from_m,to_m) values (13,4,2024,5,355);
commit;

select * from table_b;

       ID    ROAD_ID       YEAR     FROM_M       TO_M
---------- ---------- ---------- ---------- ----------
        1          1       1995          0        100
        2          1       2001          0         50
        3          1       2005         20         80
        4          1       2005          0        100
        5          1       2010          0         10
        6          1       2010         90        100
        7          1       2010          5         85
        8          1       2015         40        100
        9          1       2015          0         40
       10          2       2020          0         41
       11          3       2025        155        200
       12          3       2025        199        300
       13          4       2024          5        355

我想你想要這個，一個反半連接（也通常稱為**“反連接”**）。
“***半”***是因為 a 和 b 之間存在連接（在子查詢中），但我們只想從結果中的一個表 (b) 中獲取行。“***反”***是因為我們從一個表中獲取的行在另一表中沒有相關行：
select b.*                             -- show everything
from table_b b                         -- from table b
where not exists                       -- when
     ( select 1                       -- there is nothing
       from table_a a                 -- in table a with
       where a.road_id = b.road_id    -- same road,
         and a.year = b.year          -- same year and
         and a.from_m &lt; b.to_m        -- overlapping
         and b.from_m &lt; a.to_m        -- (from, to)
     ) ;
在sqlfiddle.com進行測試。