重叠预订 SQL [英] Overlapping Booking SQL

问题描述

我的 sql 语句有一个类似的问题，比如这里 房间预订查询.

I have a similiar problem with my sql statement like here Room Booking Query.

如果公寓只有一个预订，则以下查询有效.但如果一个公寓有多个预订，这间公寓也在结果中，虽然它在请求的时间范围内不可用.

the following query works if an apartment has only one booking. but if an apartment has more than one booking, this apartment is also in the result, although it is not available in the requested time range.

SELECT DISTINCT `apartment`.*
FROM `apartment` `apartment`
LEFT JOIN `booking` `booking` ON `apartment`.`uid` = `booking`.`apartment`
WHERE (
    NOT(
        ( `booking`.`start` <= '2018-07-23')
        AND
        ( `booking`.`end` >= '2018-07-21')
    )
)

有人可以帮我写正确的sql吗?

更新:根据 Matt Raines 的提示，我在预订表中添加了一个带有公寓 uid 的外地公寓.我非常感谢任何帮助我编写正确 SQL 语句的建议！

UPDATE: According the hint of Matt Raines i added a field apartment, with the uid of the apartment, to the booking table. I'm very thankful for any suggestion which helps me to write the right SQL statement!

这里是更新的演示数据:

--
-- Table structure for table `apartment`
--
CREATE TABLE `apartment` (
  `uid` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `bookings` int(10) UNSIGNED NOT NULL DEFAULT '0'
)
--
-- Data for table `tx_apartments_domain_model_apartment`
--
INSERT INTO `apartment` (`uid`, `title`, `bookings`) VALUES
(1, 'Apartment 1', 2),
(2, 'Apartment 2', 1),
(3, 'Apartment 3', 1),
(4, 'Apartment 4', 1);

--
-- Table structure for table `booking`
--
CREATE TABLE `booking` (
  `uid` int(11) NOT NULL,
  `start` date DEFAULT '0000-00-00',
  `end` date DEFAULT '0000-00-00',
  `apartment` int(10) UNSIGNED NOT NULL DEFAULT '0'
)
--
-- Data for table `booking`
--
INSERT INTO `booking` (`uid`, `start`, `end`, `apartment`) VALUES
(1, '2018-07-18', '2018-07-20', 1),
(2, '2018-07-21', '2018-07-23', 1),
(3, '2018-07-18', '2018-07-20', 2);

推荐答案

我认为您正在寻找在相关日期范围内没有任何预订的公寓列表.

I think you are looking for a list of apartments that do not have any bookings in the date range in question.

相反，您的查询会查找至少一项预订但不在在相关日期范围内的公寓.

Your query, instead, looks for apartments that have at least one booking which is not in the date range in question.

您链接到的问题的答案应该有效，但您也可以尝试反转问题来查找确实在日期范围内预订的公寓.然后使用 LEFT JOIN 和 WHERE booking.uid IS NULL 过滤掉这些结果.

The answers to the question you have linked to should work, but you could also try reversing the question to find apartments that do have a booking in the date range. Then use a LEFT JOIN and a WHERE booking.uid IS NULL to filter out those results.

SELECT apartment.*
FROM apartment
LEFT JOIN booking ON apartment.uid = booking.apartment
                  AND booking.start <= '2018-07-23' AND booking.end >= '2018-07-21'
WHERE booking.uid IS NULL

您可能还想考虑为该 booking.apartment 字段添加外键.至少，它应该与 apartment.uid 的数据类型相同(目前一个是 INT(10) UNSIGNED，另一个是 INT(11)).

You might also want to look into adding a foreign key for that booking.apartment field. At the very least, it should be the same datatype as apartment.uid (at the moment one is an INT(10) UNSIGNED and the other is an INT(11)).

预订的 start 和 end 日期可能应该是 NOT NULL，除非您可以进行没有日期的预订.apartment.bookings 字段现在看起来是多余的.

The start and end dates for the booking should probably be NOT NULL, unless you can have a booking without dates. And the apartment.bookings field now looks redundant.

这篇关于重叠预订 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！