如何用SQL检查连续行的日期区间是否存在间隔？

要检查连续行之间的日期区间是否存在间隔，核心思路是先按日期排序，再关联上一行的结束日期，最后对比当前行开始日期与上一行结束日期的关系。以下是具体实现方案：

核心逻辑 #

1. 按区间的结束日期（或开始日期）升序排序，确保行的顺序是时间上的连续序列
2. 使用窗口函数LAG()获取每一行的上一行结束日期
3. 判断当前行的开始日期是否晚于上一行结束日期的次日——如果是，说明两者之间存在间隔

通用SQL模板（以Oracle为例） #

假设你的表名为date_ranges，日期字段为start_date和end_date：

-- 筛选出存在间隔的行
SELECT
  start_date,
  end_date,
  previous_end_date,
  start_date - previous_end_date AS gap_days -- 计算间隔天数
FROM (
  -- 先关联上一行的结束日期
  SELECT
    start_date,
    end_date,
    LAG(end_date) OVER (ORDER BY end_date) AS previous_end_date
  FROM date_ranges
) t
WHERE previous_end_date IS NOT NULL -- 排除第一行（无上行数据）
  AND start_date > previous_end_date + 1; -- 当前行开始日期晚于上行结束日次日，说明有间隔

不同数据库的适配调整 #

如果你的数据库是MySQL或PostgreSQL，只需调整日期运算的语法：

MySQL版本 #

SELECT
  start_date,
  end_date,
  previous_end_date,
  DATEDIFF(start_date, previous_end_date) AS gap_days
FROM (
  SELECT
    start_date,
    end_date,
    LAG(end_date) OVER (ORDER BY end_date) AS previous_end_date
  FROM date_ranges
) t
WHERE previous_end_date IS NOT NULL
  AND start_date > DATE_ADD(previous_end_date, INTERVAL 1 DAY);

PostgreSQL版本 #

SELECT
  start_date,
  end_date,
  previous_end_date,
  start_date - previous_end_date AS gap_days
FROM (
  SELECT
    start_date,
    end_date,
    LAG(end_date) OVER (ORDER BY end_date) AS previous_end_date
  FROM date_ranges
) t
WHERE previous_end_date IS NOT NULL
  AND start_date > previous_end_date + INTERVAL '1 day';

处理字符串格式的日期 #

如果你的日期是以字符串形式存储（如示例中的06/FEB/23），需要先转换为日期类型再进行比较：

Oracle字符串转日期 #

SELECT
  TO_DATE(start_date, 'DD/MON/RR') AS start_date,
  TO_DATE(end_date, 'DD/MON/RR') AS end_date,
  LAG(TO_DATE(end_date, 'DD/MON/RR')) OVER (ORDER BY TO_DATE(end_date, 'DD/MON/RR')) AS previous_end_date
FROM date_ranges;

MySQL字符串转日期 #

SELECT
  STR_TO_DATE(start_date, '%d/%b/%y') AS start_date,
  STR_TO_DATE(end_date, '%d/%b/%y') AS end_date,
  LAG(STR_TO_DATE(end_date, '%d/%b/%y')) OVER (ORDER BY STR_TO_DATE(end_date, '%d/%b/%y')) AS previous_end_date
FROM date_ranges;

PostgreSQL字符串转日期 #

SELECT
  TO_DATE(start_date, 'DD/Mon/RR') AS start_date,
  TO_DATE(end_date, 'DD/Mon/RR') AS end_date,
  LAG(TO_DATE(end_date, 'DD/Mon/RR')) OVER (ORDER BY TO_DATE(end_date, 'DD/Mon/RR')) AS previous_end_date
FROM date_ranges;

内容的提问来源于stack exchange，提问作者chk.buddi