时间戳

首页 > 解决方案 > 从搜索房间是否已预订的 SQL 查询中排除值

mysql - 从搜索房间是否已预订的 SQL 查询中排除值

问题描述

我正在尝试让酒店预订系统返回未预订且特定房间大小的房间的房间 ID（我将其用作房间号），在本例中为 Single。

这是我目前正在使用的

SELECT * 
FROM rooms 
WHERE room_id NOT IN(SELECT room_id 
                     FROM room_bookings 
                     WHERE '2021/04/27' BETWEEN check_in_date AND check_out_date OR '2021/04/30' BETWEEN check_in_date AND check_out_date) 
      AND NOT EXISTS (SELECT * FROM rooms WHERE room_size = 'Double')

返回当前未预订的房间 ID 有效，但我希望它只返回单人房间 ID，不包括双人间。

我需要在此查询中添加什么以使其排除双人房？

CREATE TABLE `rooms` (
'room_id` int(11) NOT NULL,
`room_size` varchar(255) NOT NULL,
`floor_number` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `rooms` (`room_id`, `room_size`, `floor_number`) VALUES
(1, 'UNASSIGNED', 'UNASSIGNED'),
(2, 'Double', 'First'),
(3, 'Single', 'First'),
(4, 'Double', 'First'),
(5, 'Single', 'First'),
(6, 'Double', 'Second'),
(7, 'Single', 'Second'),
(8, 'Double', 'Second'),
(9, 'Single', 'Second'),
(10, 'Double', 'Second');


CREATE TABLE `room_bookings` (
`booking_id` int(11) NOT NULL,
`customer_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`manager_id` int(11) NOT NULL,
`check_in_date` date NOT NULL,
`check_out_date` date NOT NULL,
`booking_date` date NOT NULL,
`room_size` varchar(255) NOT NULL,
`lunch` varchar(255) NOT NULL,
`dinner` varchar(255) NOT NULL,
`price` varchar(255) NOT NULL,
`booking_status` varchar(255) NOT NULL,
`payment_status` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


INSERT INTO `room_bookings` (`booking_id`, `customer_id`, `room_id`, `manager_id`, `check_in_date`, `check_out_date`, `booking_date`, `room_size`, `lunch`, `dinner`, `price`, `booking_status`, `payment_status`) VALUES
(9, 6, 1, 1, '2021-04-27', '2021-04-30', '2021-04-27', 'Double', '2', '2', '£230', 'Pending', 'test'),
(10, 6, 3, 1, '2021-04-29', '2021-05-04', '2021-04-27', 'Double', '3', '4', '£465', 'Pending', 'test'),
(11, 6, 7, 1, '2021-04-28', '2021-04-30', '2021-04-27', 'Double', '0', '0', '£120', 'Pending', 'test'),
(12, 6, 1, 1, '2021-04-29', '2021-05-04', '2021-04-27', 'Double', '2', '2', '£350', 'Pending', 'No Payment');

标签： mysqlsql

解决方案

问题出在这句话上：

AND NOT EXISTS (SELECT * FROM rooms WHERE room_size = 'Double')

NOT EXISTS如果查询返回 0 个结果，则通过返回“真”来工作。只要您有任何“双人房”的房间，查询就会返回结果并且条件将失败。要使用此方法，您可以链接到子查询中的表，通过将主查询中的“rooms”表别名为“r”，将子查询别名为“d”，来挑选出特定的房间。这将确保不存在别名为“d”的“房间”行，该行与您在主查询中查看为“r”的行 ID 相同，并且大小为“双”。

SELECT * 
FROM rooms r
WHERE room_id NOT IN(SELECT room_id 
                     FROM room_bookings 
                     WHERE '2021/04/27' BETWEEN check_in_date AND check_out_date
                        OR '2021/04/30' BETWEEN check_in_date AND check_out_date) 
      AND NOT EXISTS (SELECT room_id FROM rooms d 
                      WHERE d.room_size = 'Double'
                        AND d.room_id = r.room_id)

另一种选择是像预订房间一样使用“in”子句

SELECT * 
FROM rooms r
WHERE room_id NOT IN (SELECT room_id 
                     FROM room_bookings 
                     WHERE '2021/04/27' BETWEEN check_in_date AND check_out_date
                        OR '2021/04/30' BETWEEN check_in_date AND check_out_date) 
  AND room_id NOT IN (SELECT room_id 
                     FROM rooms
                     WHERE room_size = 'Double')

但是您已经在主查询中查看“房间”表，并且room_id似乎是主键，尽管它没有显示出来。如果表中只有一个房间room_id，则您已经可以访问该房间行中的数据。因此，您可以只过滤room_size该行的值。只要没有重复的room_id行，其中一个为“单”，另一个为“双”，这将起作用。在这种情况下，“单”行仍会显示在此处：

SELECT * 
FROM rooms r
WHERE room_id NOT IN (SELECT room_id 
                     FROM room_bookings 
                     WHERE '2021/04/27' BETWEEN check_in_date AND check_out_date
                        OR '2021/04/30' BETWEEN check_in_date AND check_out_date) 
  AND room_size <> 'Double'

推荐阅读