表1中的深度边界设置错误。你不能（即 * shouldn 't *）具有与开始和结束深度相同的深度设置，因此我在示例数据中修复了这一点。

SQL> with
  2  table1 (location, seq_number, start_Depth, end_Depth) as
  3    (select 'X', 1,  100,  300 from dual union all
  4     select 'X', 2,  301,  600 from dual union all
  5     select 'X', 3,  601,  850 from dual union all
  6     select 'X', 4,  851, 1000 from dual union all
  7     select 'X', 5, 1001, 1500 from dual union all
  8     select 'X', 6, 1501, 2000 from dual
  9    ),
 10  table2 (location, measured_depth) as
 11    (select 'X',  150 from dual union all
 12     select 'X',  400 from dual union all
 13     select 'X',  850 from dual union all
 14     select 'X',  900 from dual union all
 15     select 'X', 1100 from dual union all
 16     select 'X', 1175 from dual
 17    )

字符串
查询从这里开始：

18  select b.location, b.measured_depth, a.seq_number
 19  from table2 b join table1 a on a.location = b.location
 20                             and b.measured_Depth between a.start_depth and a.end_Depth;

LOCATION   MEASURED_DEPTH SEQ_NUMBER
---------- -------------- ----------
X                     150          1
X                     400          2
X                     850          3
X                     900          4
X                    1100          5
X                    1175          5

6 rows selected.

SQL>

型

[编辑]

如果你不能修复边界，那么-而不是between比较每个深度：

18     select b.location, b.measured_depth, a.seq_number
 19     from table2 b join table1 a on a.location = b.location
 20                                and b.measured_depth > a.start_Depth
 21                                and b.measured_depth <= a.end_depth;

LOCATION   MEASURED_DEPTH SEQ_NUMBER
---------- -------------- ----------
X                     150          1
X                     400          2
X                     850          3
X                     900          4
X                    1100          5
X                    1175          5

6 rows selected.

SQL>

型

使深度范围的下限不包含：

SELECT t2.*, t1.sequence_number
FROM   table2 t2
       INNER JOIN table1 t1
       ON (   t1.location       =  t2.location
          AND t1.start_depth    <  t2.measured_depth
          AND t2.measured_depth <= t1.end_depth );

字符串
然后，对于样本数据：

CREATE TABLE table1 (location, sequence_number, start_depth, end_depth) AS
SELECT 'X', 1,  100,  300 FROM DUAL UNION ALL
SELECT 'X', 2,  300,  600 FROM DUAL UNION ALL
SELECT 'X', 3,  600,  850 FROM DUAL UNION ALL
SELECT 'X', 4,  850, 1000 FROM DUAL UNION ALL
SELECT 'X', 5, 1000, 1500 FROM DUAL UNION ALL
SELECT 'X', 6, 1500, 2000 FROM DUAL;

CREATE TABLE table2 (dt, location, value, measured_depth) AS
SELECT DATE '2023-01-01', 'X', 0.01,  150 FROM DUAL UNION ALL
SELECT DATE '2023-01-01', 'X', 0.02,  400 FROM DUAL UNION ALL
SELECT DATE '2023-01-05', 'X', 0.02,  850 FROM DUAL UNION ALL
SELECT DATE '2023-01-06', 'X', 0.01,  900 FROM DUAL UNION ALL
SELECT DATE '2023-01-06', 'X', 0.02, 1100 FROM DUAL UNION ALL
SELECT DATE '2023-01-06', 'X', 0.02, 1175 FROM DUAL;

型
输出：
| 地点|价值|测量深度|序列号| SEQUENCE_NUMBER |
| --|--|--|--| ------------ |
| X型|0.01|一百五十|一个| 1 |
| X型|0.02|四百|二个| 2 |
| X型|0.02|八百五十|三个| 3 |
| X型|0.01|九百|四个| 4 |
| X型|0.02|一千一百|五个| 5 |
| X型|0.02|一一七五|五个| 5 |
fiddle