如何查询单日提前退房后重复预订的客房及优化MySQL查询

问题拆解 #

你要找的是**同一客房在指定日期因前序订单提前退房，导致当天被重复预订、入住率超100%**的情况，核心是按客房分组，统计当天该房间的总占用时长是否超过1天。

原查询的问题 #

你的原查询存在几个致命问题：

• 重复写了两次完全相同的OR条件，纯冗余；
• 没有按客房ID分组，根本无法判断同一个房间是否被多订单覆盖；
• 日期筛选逻辑不严谨，没覆盖「前序订单当天退房+新订单当天入住」的核心场景；
• 缺少入住率统计逻辑，没法直接判断是否超100%。

优化后的查询方案 #

1. 找出所有入住率超100%的客房（带统计数据） #

这个查询会计算每个客房在目标日期的总入住时长，筛选出超过1天（即100%）的客房：

-- 设置目标日期，可根据需求修改
SET @target_date = '2024-01-01';

WITH overlapping_orders AS (
    SELECT 
        room_id,
        order_id,
        -- 计算单个订单在目标日期的占用时长（按天为单位）
        CASE
            -- 订单完全覆盖目标日期（比如前一天入住，后一天退房）
            WHEN checkin <= @target_date AND checkout >= DATE_ADD(@target_date, INTERVAL 1 DAY) THEN 1
            -- 订单之前入住，当天退房（提前退房的情况）
            WHEN checkin <= @target_date AND checkout > @target_date AND checkout < DATE_ADD(@target_date, INTERVAL 1 DAY) THEN TIMESTAMPDIFF(HOUR, @target_date, checkout)/24
            -- 订单当天入住，之后退房（新预订的情况）
            WHEN checkin > @target_date AND checkin < DATE_ADD(@target_date, INTERVAL 1 DAY) AND checkout >= DATE_ADD(@target_date, INTERVAL 1 DAY) THEN TIMESTAMPDIFF(HOUR, checkin, DATE_ADD(@target_date, INTERVAL 1 DAY))/24
            -- 订单当天入住当天退房
            WHEN checkin >= @target_date AND checkout <= DATE_ADD(@target_date, INTERVAL 1 DAY) THEN TIMESTAMPDIFF(HOUR, checkin, checkout)/24
            ELSE 0
        END AS daily_occupancy
    FROM orders
    -- 标准日期重叠判断：只要订单时间段和目标日期（当天0点到次日0点）有交集就保留
    WHERE checkin < DATE_ADD(@target_date, INTERVAL 1 DAY) 
      AND checkout > @target_date
)
-- 按客房分组，筛选总入住率超100%的记录
SELECT 
    room_id,
    COUNT(order_id) AS 当日订单数,
    ROUND(SUM(daily_occupancy)*100, 2) AS 总入住率(%)
FROM overlapping_orders
GROUP BY room_id
HAVING SUM(daily_occupancy) > 1;

2. 查看超员客房的具体订单明细 #

如果需要知道每个超员客房对应的具体订单（比如你例子中的101房A2、A3订单），用这个查询：

SET @target_date = '2024-01-01';

-- 先筛选出所有入住率超100%的客房ID
WITH over_occupied_rooms AS (
    SELECT room_id
    FROM orders
    WHERE checkin < DATE_ADD(@target_date, INTERVAL 1 DAY) 
      AND checkout > @target_date
    GROUP BY room_id
    HAVING SUM(
        CASE
            WHEN checkin <= @target_date AND checkout >= DATE_ADD(@target_date, INTERVAL 1 DAY) THEN 1
            WHEN checkin <= @target_date AND checkout > @target_date THEN TIMESTAMPDIFF(HOUR, @target_date, checkout)/24
            WHEN checkin > @target_date AND checkout >= DATE_ADD(@target_date, INTERVAL 1 DAY) THEN TIMESTAMPDIFF(HOUR, checkin, DATE_ADD(@target_date, INTERVAL 1 DAY))/24
            ELSE TIMESTAMPDIFF(HOUR, checkin, checkout)/24
        END
    ) > 1
)
-- 关联订单表获取具体订单信息
SELECT 
    o.room_id,
    o.order_id,
    o.checkin,
    o.checkout
FROM orders o
JOIN over_occupied_rooms oor ON o.room_id = oor.room_id
WHERE o.checkin < DATE_ADD(@target_date, INTERVAL 1 DAY) 
  AND o.checkout > @target_date
ORDER BY o.room_id, o.checkin;

关键逻辑说明 #

• 用DATE_ADD(@target_date, INTERVAL 1 DAY)把目标日期转换成次日0点，确保日期重叠判断的准确性；
• 通过CASE语句计算每个订单在目标日期的实际占用时长，避免把「提前退房+当天新入住」的情况误判为正常入住；
• 按客房分组后，总占用时长超过1天就意味着入住率超100%，符合你要找的重复预订场景。

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