数据库中数字范围的间隙查找方法技术问询

嘿，很高兴你已经搞定了判断数字是否落在区间内的查询问题！现在你想找出现有数字区间之间的间隙，这个需求在数据整理、范围校验场景里特别常见，我来给你分享几种实用的SQL实现方法。

方法一：用窗口函数快速找中间间隙 #

如果你的区间没有重叠或者已经处理过重叠，用LEAD()窗口函数可以轻松定位相邻区间的间隙：

SELECT
  r1.EndingNumber + 1 AS GapStart,
  r2.StartingNumber - 1 AS GapEnd
FROM (
  -- 给每个区间标记下一个区间的起始值
  SELECT
    StartingNumber,
    EndingNumber,
    LEAD(StartingNumber) OVER (ORDER BY StartingNumber) AS NextIntervalStart
  FROM range
) r1
-- 关联下一个区间
JOIN range r2 ON r2.StartingNumber = r1.NextIntervalStart
-- 只筛选存在间隙的情况：下一个区间的起始 > 当前区间的结束 + 1
WHERE r2.StartingNumber > r1.EndingNumber + 1;

这段代码的逻辑很直观：先按起始值排序每个区间，然后获取每个区间的“下一个区间起始值”，如果下一个区间的起始值比当前区间的结束值大1以上，中间的部分就是我们要找的间隙。

方法二：包含首尾边界的完整间隙查询 #

如果需要同时检查开头（比如从0到第一个区间起始的间隙）和结尾（最后一个区间结束到某个上限的间隙），可以用UNION ALL拼接三个查询：

-- 1. 检查开头是否存在间隙（假设我们的数值从0开始，可根据实际调整）
SELECT
  0 AS GapStart,
  MIN(StartingNumber) - 1 AS GapEnd
FROM range
WHERE MIN(StartingNumber) > 0

UNION ALL

-- 2. 检查中间的区间间隙
SELECT
  r1.EndingNumber + 1 AS GapStart,
  r2.StartingNumber - 1 AS GapEnd
FROM (
  SELECT
    StartingNumber,
    EndingNumber,
    LEAD(StartingNumber) OVER (ORDER BY StartingNumber) AS NextIntervalStart
  FROM range
) r1
JOIN range r2 ON r2.StartingNumber = r1.NextIntervalStart
WHERE r2.StartingNumber > r1.EndingNumber + 1

UNION ALL

-- 3. 检查结尾是否存在间隙（假设我们的数值上限是10000，可根据实际调整）
SELECT
  MAX(EndingNumber) + 1 AS GapStart,
  10000 AS GapEnd
FROM range
WHERE MAX(EndingNumber) < 10000;

方法三：先合并重叠/相邻区间，再找间隙 #

如果你的原始区间存在重叠或者相邻的情况（比如[100,200]和[150,300]，或者[200,300]和[301,400]），建议先合并这些区间，再找间隙，避免结果混乱：

WITH MergedRanges AS (
  -- 第一步：筛选出所有不被其他区间包含的起始区间
  SELECT
    StartingNumber,
    EndingNumber
  FROM range r1
  WHERE NOT EXISTS (
    SELECT 1
    FROM range r2
    WHERE r2.StartingNumber < r1.StartingNumber 
      AND r2.EndingNumber >= r1.EndingNumber
  )
  -- 第二步：递归合并重叠或相邻的区间
  UNION ALL
  SELECT
    m.StartingNumber,
    GREATEST(m.EndingNumber, r.EndingNumber)
  FROM MergedRanges m
  JOIN range r ON r.StartingNumber BETWEEN m.StartingNumber AND m.EndingNumber + 1
  WHERE r.EndingNumber > m.EndingNumber
)
-- 去重合并后的区间
SELECT DISTINCT StartingNumber, EndingNumber FROM MergedRanges ORDER BY StartingNumber;

得到合并后的区间后，再用方法一或方法二的逻辑去查找间隙就准确多了。

内容的提问来源于stack exchange，提问作者Joe