选择数字范围不重叠的地方 [英] Select where number range does not overlap
问题描述
我有两个表，其中包含有关道路建设活动的记录:
I have two tables that contain records about road construction activites:
-
table_a是主列表. -
table_b是旧列表.
table_ais the master list.table_bis a legacy list.
对于每年的每条道路，我想从table_b中选择table_a中不存在的记录.
For each road, in each year, I want to select the records from table_b that do not already exist in table_a.
此外，记录应该不沿道路在空间上重叠.更具体地说，table_b中的记录的from_m和to_m不应与table_a中的from_m和to_m重叠.
Also, the records should not overlap spatially along the road. More specifically, the from_m and to_m of the records in table_b should not overlap the from_m and to_m in table_a.
我该怎么做?我没有Oracle Spatial.
How can I do this? I do not have Oracle Spatial.
Excel中的数据(为了便于查看):
这是Excel中数据的样子:
Here is what the data looks like in Excel:
绿色中的记录应由查询选择； 红色中的记录不应该.
The records in green should be selected by the query; the records in red should not.
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:
Table 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 NOT EXISTS sub-select can help here
SELECT *
FROM table_b b
WHERE
NOT EXISTS (SELECT *
FROM table_a a
WHERE
a.road_id = b.road_id AND
a.year = b.year AND
a.to_m > b.from_m AND
a.from_m < b.to_m)
让我们看一下重叠的范围(f = from，t = to)
Let's look at overlapping ranges (f=from, t=to)
a -------------------f=======================t-----------------
b1a -----f=============t-----------------------------------------
b1b --f=============t--------------------------------------------
b2a -------------------------------------------f======t----------
b2b -----------------------------------------------f======t------
b3 ---------------f=========t-----------------------------------
b4 ------------------------f===========t------------------------
b5 ---------------------------------------f===========t---------
范围b3，b4和b5重叠.对于他们所有人来说，以下都是正确的
The ranges b3, b4 and b5 overlap. for all of them the following is true
a.to > b.from && a.from < b.to
对于不重叠此条件的b1a，b1b和b2a，b2b，则为false.对于b1a a.from == b.to，对于b1b a.from > b.to，因此条件a.from < b.to是false.
For b1a, b1b and b2a, b2b that don't overlap this condition is false. For b1a a.from == b.to, for b1b a.from > b.to therefore the condition a.from < b.to is false.
对于b2a a.to == b.from，对于b2b a.to < b.from，因此条件a.to > b.from为false.
For b2a a.to == b.from, for b2b a.to < b.from therefore the condition a.to > b.from is false.
诀窍是比较一个范围的from与另一个范围的to，反之亦然.
The trick is to compare the from of one range with the to of the other one and vice-versa.
请参阅: http://sqlfiddle.com/#!4/85883/3 /0
这篇关于选择数字范围不重叠的地方的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
