如何识别重叠编号范围

Question

我有两个表 TABLE1(记录列表(和 TABLE2(我要检查的数字范围(

如何识别表2中的重叠范围/行？

目的

RECORDS TABLE
+-----------------+-------------------+
+ tbl1_NumberFrom + tbl1_NumberTo     +
+-----------------+-------------------+
+      0          +        10         +
+      15         +        20         +
+      50         +        100        +
+-----------------+-------------------+
MY RANGES TABLE
+-----------------+-------------------+
+ My_NumberFrom   + My_NumberTo       +
+-----------------+-------------------+
+      5          +        20         +
+      30         +        40         +
+      12         +        15         +
+-----------------+-------------------+
REQUIRED RESULT AS FOLLOW:
+-----------------+-------------------+--------------+
+ My_NumberFrom   + My_NumberTo       + IsOverlaping +
+-----------------+-------------------+--------------+
+      5          +        20         +      Yes     +
+      30         +        40         +      No      +
+      12         +        15         +      Yes     +
+-----------------+-------------------+--------------+

此外，

以上表的 SQL

IF OBJECT_ID('tempdb.dbo.#dbtable', 'U') IS NOT NULL  DROP TABLE #dbtable; 
SELECT * INTO #dbtable FROM 
    (SELECT 0 AS tbl1_NumberFrom, 10 AS tbl1_NumberTo
    UNION ALL 
    SELECT 15 AS tbl1_NumberFrom, 20 AS tbl1_NumberTo
    UNION ALL 
    SELECT 50 AS tbl1_NumberFrom, 100 AS tbl1_NumberTo) 
AS dbTable
IF OBJECT_ID('tempdb.dbo.#MyRanges', 'U') IS NOT NULL  DROP TABLE #MyRanges; 
SELECT * INTO #MyRanges FROM 
    (SELECT 5 AS MyTbl_NumberFrom, 20 AS MyTbl_NumberTo
    UNION ALL 
    SELECT 30 AS MyTbl_NumberFrom, 40 AS MyTbl_NumberTo 
    UNION ALL 
    SELECT 12 AS MyTbl_NumberFrom, 15 AS MyTbl_NumberTo)
AS MyList

SELECT * FROM #dbtable t
SELECT * FROM #MyRanges m

恳请建议，

非常感谢 ！！

Answer 1

使用外部连接查看是否存在重叠。这三个条件将涵盖所有类型的重叠。

Select distinct m.MyTbl_NumberFrom,m.MyTbl_NumberTo, 
    case when t.tbl1_NumberFrom is null then 'No' else 'yes' end isOverlapping
from #MyRanges m
left join #dbtable t on m.MyTbl_NumberFrom between t.tbl1_NumberFrom and t.tbl1_NumberTo
                or  m.MyTbl_Numberto between t.tbl1_NumberFrom and t.tbl1_NumberTo
                or  t.tbl1_NumberFrom between m.MyTbl_NumberFrom and m.MyTbl_Numberto