选择不在日期之间的行(保留) [英] Select rows that are not between dates (reservation)

问题描述

我有一个租车脚本.我把汽车放在汽车桌旁.我将所有预订信息都保存在预订表中.预订表包含从"和直到"日期字段以及car_id字段.

I have a car rental script. I have the cars in a cars table. I keep all the reservation information in a reservation table. Reservation table has from and until date fields as well as car_id field.

例如:car_id:14，from ="2014-06-24"，直到="2014-07-24".

For example: car_id: 14, from="2014-06-24", until="2014-07-24".

我正在尝试编写查询以获取给定日期之间未保留的所有汽车.我尝试了一些技巧，例如内部联接，但不存在等.

I am trying to write a query to fetch all cars that are not reserved between given dates. I have tried a couple of tricks like inner join and not exists etc.

所以，说我想获取日期"2014-06-24"和"2014-08-24"之间的可用汽车.

So, say I am trying to fetch the available cars between dates "2014-06-24" and "2014-08-24".

为简化起见，使我的问题更明确:使用此查询选择保留的汽车不起作用:

For simplifying purposes to make my problem clear: selecting reserved cars using this query doesn't work:

SELECT * FROM reservation WHERE from >= '2014-06-24' AND until <= '2014-08-24'

因为从'2014-06-30'到'2014-09-15'可能会预订汽车.由于查询中的截止日期不大于或等于截止日期，因此汽车不会显示为已保留.

Because a car might be booked from '2014-06-30' until '2014-09-15'. Since the until date is not greater than or equal to until date in the query the car doesn't show up as reserved.

希望您能理解我的观点.预先感谢.

I hope you can understand my point. Thanks in advance.

由于Marc B的回答，我找到了所要查询的查询:

I found the query that I was looking for thanks to Marc B's answer:

SELECT * FROM cars WHERE NOT EXISTS (
SELECT * FROM reservation WHERE cars.id = reservation.car_id AND until >= '2014-06-24' AND from <= '2014-08-24')

推荐答案

请考虑以下情况.您已经预订了日期A和B，并且要预订的两个日期P和Q.这意味着可以满足以下条件:

Consider the following cases. You've got your already reserved dates A and B, and your two dates that you're trying to book for, P and Q. That means the following conditions can be true:

A B P Q    date comes AFTER 'already booked'
A P B Q  * dates overlap
A P Q B  * dates are entirely overlapped
P A Q B  * dates overlap
P Q A B    date comes BEFORE 'already booked'

带有*的条目是您感兴趣的条目，一旦您压缩了所有比较和逻辑，便得出了

The entries with * are the ones you're interested in, and once you crunch all the comparisons and logic down, you end up with

P <= B && Q >= A

这篇关于选择不在日期之间的行(保留)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！