重叠预订查询 [英] Overlapping Booking Query

问题描述

我的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);

推荐答案

请考虑以下内容.

DROP TABLE IF EXISTS apartment;

CREATE TABLE apartment
(apartment_id SERIAL PRIMARY KEY
,apartment_name varchar(255) NOT NULL 
);

INSERT INTO apartment VALUES
(1, 'Apartment 1'),
(2, 'Apartment 2'),
(3, 'Apartment 3'),
(4, 'Apartment 4');

DROP TABLE IF EXISTS booking;

CREATE TABLE booking 
(booking_id SERIAL PRIMARY KEY
,start_date DATE NOT NULL
,end_date DATE NOT NULL
,apartment_id INT NOT NULL
);

INSERT INTO booking 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);



SELECT a.* 
  FROM apartment a 
  LEFT 
  JOIN booking b 
    ON b.apartment_id = a.apartment_id 
   AND b.start_date <= '2018-07-23' 
   AND b.end_date > '2018-07-21'
 WHERE b.booking_id IS NULL;
+--------------+----------------+
| apartment_id | apartment_name |
+--------------+----------------+
|            2 | Apartment 2    |
|            3 | Apartment 3    |
|            4 | Apartment 4    |
+--------------+----------------+

实际上，除非绑定在交易中，否则SELECT这样的选择是毫无意义的，因为在您仍在检查可用性时，另一个用户可以轻松地进行预订.

In practice, unless bound up within a transaction, a SELECT like this is fairly meaningless, because another user can easily come along and make the booking while you're still checking availability.

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