排除sql查询中的值，该查询搜索房间是否已预订

jhiyze9q 于 2021-07-26 发布在 Java

关注(0)|答案(5)|浏览(282)

我试图让一个酒店预订系统返回的房间id的（我使用的房间号码）的房间，没有预订，是一个特定的房间大小，在这种情况下，单人。
这是我目前正在使用的

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');

sql mysql

来源：https://stackoverflow.com/questions/67287668/excluding-values-from-an-sql-query-that-searches-if-a-room-is-booked

5条答案

按热度按时间

cyvaqqii1#

问题在于这种说法：

AND NOT EXISTS (SELECT * FROM rooms WHERE room_size = 'Double')
``` `NOT EXISTS` 如果查询返回0个结果，则返回“true”。只要有“double”的房间，查询就会返回结果，条件就会失败。要使用此方法，您可以链接到子查询中的表，通过将主查询中的“rooms”表别名为“r”，将子查询别名为“d”来挑出特定的房间。这将确保不存在别名为“d”的“rooms”行，该行与您在主查询中看到的“r”行id相同，并且大小为“double”。

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')

但是您已经在主查询中查看了“rooms”表，并且 `room_id` 似乎是主键，尽管它没有显示为主键。如果只有一个房间 `room_id` 在表中，您已经可以访问文件室行中的数据。所以你可以直接在 `room_size` 该行的值。只要没有重复，这就行 `room_id` 一行为“single”，另一行为“double”。在这种情况下，“single”行仍会显示在这里：

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'

赞(0）回复(0）举报 2021-07-26

lg40wkob2#

在创建table房间时出现了一个小错误
你可以把所有的人都联合起来

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)
       UNION 
       SELECT  `room_id` FROM `rooms` wHERE
            room_size = 'Double')

赞(0）回复(0）举报 2021-07-26

km0tfn4u3#

你的 EXISTS 条款不合适。你只想要单人房，所以用吧 WHERE room_size = 'Single' :

SELECT * 
FROM rooms 
WHERE room_size = 'Single'
AND room_id NOT IN
(
  SELECT room_id 
  FROM room_bookings 
  WHERE DATE '2021-04-27' BETWEEN check_in_date AND check_out_date 
     OR DATE '2021-04-30' BETWEEN check_in_date AND check_out_date
);

赞(0）回复(0）举报 2021-07-26

rmbxnbpk4#

我也想出了这个解决办法

SELECT * 
FROM rooms 
WHERE room_size = 'Single' AND 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)

赞(0）回复(0）举报 2021-07-26

dsf9zpds5#

checkout 以下查询：

SELECT * FROM rooms 
WHERE room_id 
NOT IN(SELECT room_id FROM booking 
       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'

赞(0）回复(0）举报 2021-07-26

我来回答

排除sql查询中的值，该查询搜索房间是否已预订

5条答案

相关问题

热门标签

最新问答