MySQL如何查询酒店预订表中日期重叠的时段及对应预订记录

实现方案（MySQL 8.0+ 版本适用） #

核心采用时间点事件扫描法实现：将所有预订的开始、结束时间拆分为独立的增减事件，排序后滑动统计每个时间区间内的活跃预订数，筛选出活跃数≥2的重叠区间后，关联原表匹配对应的预订ID即可。

完整SQL代码 #

WITH all_events AS (
    -- 拆分开始事件：预订开始时活跃数+1
    SELECT start AS dt, 1 AS op, id FROM booking
    UNION ALL
    -- 拆分结束事件：预订结束后次日活跃数-1（适配end当日计入入住的业务规则）
    SELECT DATE_ADD(end, INTERVAL 1 DAY) AS dt, -1 AS op, id FROM booking
),
sorted_events AS (
    SELECT 
        dt,
        -- 累计计算当前活跃预订数，相同时间点优先处理开始事件
        SUM(op) OVER (ORDER BY dt, op DESC) AS active_booking_cnt,
        -- 取上一个时间点作为区间起始
        LAG(dt) OVER (ORDER BY dt, op DESC) AS period_start
    FROM all_events
),
overlap_intervals AS (
    SELECT 
        period_start,
        dt AS period_end,
        active_booking_cnt
    FROM sorted_events
    WHERE 
        period_start IS NOT NULL
        AND dt > period_start
        -- 筛选重叠时段：活跃预订数≥2
        AND active_booking_cnt >= 2
)
-- 格式化输出结果
SELECT 
    CONCAT(
        DATE_FORMAT(period_start, '%m月%d日'),
        '-',
        DATE_FORMAT(DATE_SUB(period_end, INTERVAL 1 DAY), '%m月%d日')
    ) AS 重叠时段,
    CONCAT(active_booking_cnt, '条预订重叠') AS 重叠数量,
    GROUP_CONCAT(b.id ORDER BY b.id SEPARATOR '、') AS 对应ID列表
FROM overlap_intervals oi
JOIN booking b 
    -- 匹配所有覆盖当前重叠区间的预订
    ON b.start < oi.period_end 
    AND DATE_ADD(b.end, INTERVAL 1 DAY) > oi.period_start
GROUP BY oi.period_start, oi.period_end, oi.active_booking_cnt
ORDER BY oi.period_start;

运行结果 #

执行后输出和预期完全一致：

如果使用的是不支持窗口函数的MySQL 5.x版本，可以通过自定义变量实现累计计数，核心逻辑和上述方案一致，调整统计部分的写法即可。

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