如何筛选客户所选入住退房时段外的可用客房？

需求说明 #

筛选出所有不在客户选择的入住（2023-01-12）和退房（2023-01-13）时段内的可用客房。

表结构及数据 #

ROOM表 #

CREATE TABLE `room` (
`id` int(11) NOT NULL,
`price` double NOT NULL,
`type` varchar(255) NOT NULL,
`photo` varchar(255) NOT NULL,
`max_capacity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (1, 25, 'Suite', 'something.jpg', 3);
INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (2, 20, 'Single', 'something.jpg', 1);
INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (3, 250, 'Family Suite', 'something.jpg', 8);
INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (4, 20, 'Twin', 'something.jpg', 2);
INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (5, 20, 'Twin', 'something.jpg', 2);
INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (6, 25, 'Suite', 'something.jpg', 3);

ALTER TABLE `room`
ADD PRIMARY KEY (`id`);

ORDERS表 #

CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`checkin` date NOT NULL,
`checkout` date NOT NULL,
`id_user` int(11) NOT NULL,
`id_room` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `orders` (`id`, `checkin`, `checkout`, `id_user`, `id_room`) VALUES
(1, '2023-01-12', '2023-01-13', 1, 1),
(2, '2023-01-11', '2023-01-15', 1, 2);

ALTER TABLE `orders`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

尝试的SQL语句 #

SELECT id,type FROM room  WHERE (SELECT * FROM orders  WHERE  (2023-01-12 NOT BETWEEN  `checkin` AND `checkout`) AND (2023-01-13  NOT BETWEEN `checkin` AND `checkout`));

期望结果 #

用户期望显示id为2-6的客房，不显示id=1的客房。但根据订单数据，id=2的客房在2023-01-11至2023-01-15期间已被预订，与目标时段（2023-01-12至2023-01-13）重叠，实际应排除该客房。

修正后的SQL语句及解释 #

正确SQL（符合酒店预订逻辑） #

SELECT r.id, r.type
FROM room r
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.id_room = r.id
    AND o.checkout > '2023-01-12'
    AND o.checkin < '2023-01-13'
);

关键修正点 #

1. 日期格式错误：原语句中2023-01-12未加单引号，会被MySQL当作数值计算（2023-1-12=2010），完全偏离日期判断逻辑，必须加单引号表示日期字符串。
2. 重叠判断逻辑错误：判断两个时段是否重叠，正确条件是订单退房日期 > 目标入住日期且订单入住日期 < 目标退房日期，而非简单判断单个日期是否不在订单时段内。
3. 子查询关联错误：原子查询未关联room表的id，导致逻辑混乱，NOT EXISTS子查询通过o.id_room = r.id关联客房，确保判断的是当前客房的订单情况。

执行结果 #

该语句返回id为3、4、5、6的客房，符合实际可用情况：

• id=1的订单时段与目标时段完全重合，不可用
• id=2的订单时段包含目标时段，不可用
• id=3-6无重叠订单，可用

若需按照用户原期望（保留id=2），可调整重叠判断条件，但不符合常规酒店预订规则。

内容的提问来源于stack exchange，提问作者Altin Mag.