PL / SQL :给定两个数字范围，如何检测包含或交叉

Question

>我有两个数字范围 [1， 100] 和 [50， 200] ：

1       100
50      200

• 这是一个十字路口

另一种情况：范围 [40， 100] 和 [10， 200]

40      100
10      200

• 这是一个包含

如何编写检测包含/交集的 SQL 查询(或 PL：SQL 过程(。

我尝试使用减号，但它并非在所有情况下都有效。

编辑 1

范围作为表中的行传递给过程。

Answer 1

这是一种方法：

WITH sd AS (SELECT start_range,
                   end_range,
                   row_number() OVER (ORDER BY start_range, end_range) rn
            FROM   sample_data)
SELECT sd1.*,
       sd2.*,
       CASE WHEN sd1.start_range >= sd2.start_range AND sd1.end_range <= sd2.end_range THEN 'INCLUSION'
            WHEN sd2.start_range >= sd1.start_range AND sd2.end_range <= sd1.end_range THEN 'INCLUSION'
            WHEN sd1.start_range <= sd2.end_range AND sd1.end_range >= sd2.start_range THEN 'INTERSECTION'
            ELSE 'NONE'
       END range_overlap_type
FROM   sd sd1
       INNER JOIN sd sd2 ON sd1.rn = 1 AND sd2.rn = 2;

Answer 2

with source1 as
 (select 60 f, 100 s
    from dual
  union
  select 50 f, 200 s from dual),
t as
 (select source1.*, rownum rn from source1)
select case
         /*(1, 100), (50, 90)*/
         when t1.f <= t2.f and t1.s >= t2.s then
          'Inclusion'
         /*(1, 100), (50, 150)*/
         when t1.f <= t2.f and t1.s < t2.s then
          'Intersection'
       end result
  from (select * from t where rn = 1) t1, (select * from t where rn = 2) t2

我给出了可能的两种情况作为评论