SQL-是否有任何数字范围重叠

Question

我有一个包含数字范围列表的表格。我需要知道这些数字范围是否相互重叠

SQL Fiddle here

例如， fiddle 中的最后两行相互重叠。我不确定你会如何在查询结果中显示它。

如有任何帮助，我们将不胜感激。

编辑:不确定这是否可行，但看起来是:

SELECT
  *
FROM
(
  SELECT
    FROM_NUM,
    TO_NUM,
    LEAD(FROM_NUM) OVER (ORDER BY FROM_NUM) A
  FROM
    RANGES
) RANGES
WHERE
  A < TO_NUM

Answer 1

SELECT RANGES.FROM_NUM AS BASE_FROM_NUM,  RANGES.TO_NUM AS BASE_TO_NUM,
  OVERLAP.FROM_NUM AS OVERLAP_FROM_NUM, OVERLAP.TO_NUM AS OVERLAP_TO_NUM
FROM RANGES
INNER JOIN RANGES OVERLAP 
ON (((OVERLAP.FROM_NUM <= RANGES.FROM_NUM) AND (OVERLAP.TO_NUM >= RANGES.FROM_NUM))
OR ((OVERLAP.FROM_NUM <= RANGES.TO_NUM) AND (OVERLAP.TO_NUM >= RANGES.TO_NUM)))
AND ((OVERLAP.FROM_NUM <> RANGES.FROM_NUM) AND (OVERLAP.TO_NUM <> RANGES.TO_NUM))

最后一行只是为了停止报告自己重叠的行。在实践中，您可以使用唯一键删除它们(以防万一您有具有相同起始/终止编号的重复行):

AND (OVERLAP.ID <> RANGES.ID)