基于用户输入日期查询可用房间的MySQL实现难题

我来帮你搞定这个查询逻辑！首先咱们得明确核心需求：找出standard和premier类型的房间中，在2018-05-05至2018-05-06期间没有被预订（即无时间重叠的预订记录）的房间。

先理清楚关键逻辑：时间段重叠判断 #

酒店预订的时间段重叠是查询的核心，咱们需要排除所有和目标时间段（入住2018-05-05、退房2018-05-06）有重叠的房间。两个时间段[A_start, A_end)和[B_start, B_end)重叠的判断条件是：

A_start < B_end AND A_end > B_start

这个条件能覆盖所有可能的重叠场景：

• 预订完全包含目标时间段（比如你示例中john的2018-05-03至2018-05-07）
• 预订部分覆盖目标时间段（比如预订入住2018-05-05至2018-05-08，或2018-05-04至2018-05-06）
• 目标时间段完全包含预订

假设必要的表结构 #

你只给出了Reservations表，实际酒店系统里通常还有一张Rooms表来存储房间的基础信息（类型、价格等），我们先假设表结构如下：

• Rooms表：RoomID(主键, 房间ID)、Type(房间类型)、Price(价格)
• Reservations表：ID(主键)、User(预订用户)、RoomID(外键关联Rooms表)、Check_in(入住日期)、Check_out(退房日期)

最终SQL查询语句 #

-- 定义目标查询时间段
SET @target_check_in = '2018-05-05';
SET @target_check_out = '2018-05-06';

-- 查询可用房间
SELECT 
    r.RoomID,
    r.Type,
    r.Price
FROM Rooms r
WHERE 
    -- 筛选指定房间类型
    r.Type IN ('standard', 'premier')
    -- 排除有重叠预订的房间
    AND NOT EXISTS (
        SELECT 1
        FROM Reservations res
        WHERE 
            res.RoomID = r.RoomID
            AND res.Check_in < @target_check_out
            AND res.Check_out > @target_check_in
    );

针对你提供的示例数据的效果 #

你示例中john的预订是2018-05-03至2018-05-07，这个时间段和目标时间段重叠，所以对应的房间会被排除在结果之外。

如果没有Rooms表的特殊情况 #

如果你的系统里没有单独的Rooms表，而是Reservations表直接记录了房间类型（虽然这种设计不太合理，但也能处理），你可以先找出所有出现过的standard/premier类型房间，再排除有重叠预订的：

SET @target_check_in = '2018-05-05';
SET @target_check_out = '2018-05-06';

SELECT DISTINCT
    res.RoomID,
    res.RoomType,
    -- 假设价格是固定的，可以用CASE赋值
    CASE res.RoomType 
        WHEN 'standard' THEN 300
        WHEN 'premier' THEN 900
    END AS Price
FROM Reservations res
WHERE 
    res.RoomType IN ('standard', 'premier')
    AND NOT EXISTS (
        SELECT 1
        FROM Reservations res2
        WHERE 
            res2.RoomID = res.RoomID
            AND res2.Check_in < @target_check_out
            AND res2.Check_out > @target_check_in
    );

不过还是建议使用Rooms表来存储房间基础信息，这样数据更规范易维护。

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