如何查询MySQL资源可用性表中记录间的空白时间段数据？

以下是可以实现需求的MySQL查询语句：

-- 原可用性记录
SELECT id, start_date, end_date, status
FROM availibility
UNION ALL
-- 生成两条记录之间的空白时间段
SELECT 
    NULL AS id,
    DATE_ADD(a.end_date, INTERVAL 1 DAY) AS start_date,
    DATE_ADD(b.start_date, INTERVAL -1 DAY) AS end_date,
    0 AS status
FROM availibility a
JOIN availibility b ON b.start_date > a.end_date
-- 确保只匹配当前记录之后的第一条有效记录
WHERE NOT EXISTS (
    SELECT 1 
    FROM availibility c 
    WHERE c.start_date > a.end_date AND c.start_date < b.start_date
)
-- 过滤掉无实际空白的情况（比如两条记录日期连续）
AND DATE_ADD(a.end_date, INTERVAL 1 DAY) <= DATE_ADD(b.start_date, INTERVAL -1 DAY)
-- 按时间顺序排序结果
ORDER BY start_date;

代码说明： #

• 第一部分：直接查询原表的所有可用状态记录，保留原始数据结构。
• 第二部分：通过自连接定位每条记录的下一条有效记录，计算两者之间的空白区间：
  • DATE_ADD(a.end_date, INTERVAL 1 DAY)：空白期的起始为当前记录结束日的次日
  • DATE_ADD(b.start_date, INTERVAL -1 DAY)：空白期的结束为下一条记录起始日的前一日
  • NOT EXISTS子句避免生成重复的空白区间，确保只匹配当前记录之后的第一条后续记录
  • 最后的条件过滤掉日期连续、无实际空白的情况
• ORDER BY：按起始日期排序，保证结果按时间顺序展示

执行结果： #

运行上述SQL后，将返回你期望的结果：

扩展提示： #

如果需要处理第一条记录之前、最后一条记录之后的空白期，可以添加额外查询分支：

• 第一条记录之前的空白：可根据业务需求指定起始日期（比如表中最早记录的前一日）
• 最后一条记录之后的空白：若end_date为NULL表示持续可用，无需处理；若有明确结束日，可生成到指定截止日的空白区间

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