mysql 显示重叠,mysql – 重叠预订查询

我的sql语句有一个类似问题,就像这里Room Booking Query.

如果公寓只有一个预订,则以下查询有效.但如果公寓有多个预订,这间公寓也在结果中,但在要求的时间范围内无法使用.

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的暗示,我在公寓的房子里添加了一个现场公寓到预订桌.我非常感谢任何有助于我编写正确的SQL语句的建议！

这里是更新的演示数据：

--

-- 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是毫无意义的,因为在您仍在检查可用性时,另一个用户可以轻松地进入并进行预订.

标签：sql,mysql,datetime

来源： https://codeday.me/bug/20191003/1849557.html