如何简化有关预订的查询？

Question

我有酒店预订系统，当用户输入一个新的预订日期时，我需要检索所有未预订的房间，

预订表包含room_id、开始日期、期间

我写了这个查询，只是需要简化一下。

select * 
from rooms 
where id in (
   select room_id 
   from reservations 
   where '2018-6-11' not BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   and '2018-6-30' not between date_at and date_add(date_at, INTERVAL period day) 
   and date_at not between '2018-6-11' and '2018-6-30' 
   and date_add(date_at ,INTERVAL period day ) not between '2018-6-11' and '2018-6-30' ) 
or id not in (select room_id from reservations)

Answer 1

如何将IN子句转换为等效的联接？请参见下面的查询，它是否在您的情况下有效：

SELECT *
FROM rooms rm
JOIN 
   (SELECT room_id rmid
  FROM reservations
  WHERE '2018-6-11' NOT BETWEEN date_at AND date_add(date_at, INTERVAL period DAY)
  AND '2018-6-30' NOT BETWEEN date_at AND date_add(date_at, INTERVAL period DAY)
  AND date_at NOT BETWEEN '2018-6-11' AND '2018-6-30'
  AND date_add(date_at ,INTERVAL period DAY ) NOT BETWEEN '2018-6-11' AND '2018-6-30'
  ) tb1  
ON  rm.ID = tb.rmid 
JOIN reservations rv
ON rm.ID <> rv.room_id;

Answer 2

让我们把它分解成两个子问题。

• 把所有从未预订过的房间都安排好。
• 得到所有的房间，从来没有冲突的预定房间的时间。

在查询中，我假设$start_date和$end_date是将用户选择的值存储在其中的PHP变量。

(select * 
from rooms where room_id NOT IN (select room_id from reservations))

UNION

(select *
from rooms where room_id IN (
   select room_id 
   from reservations 
   where $start_date not BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   and $end_date not between date_at and date_add(date_at, INTERVAL period day)
   and ($start_date > date_add(date_at, INTERVAL period day) OR $end_date < date_at)
))

Answer 3

您不需要执行两个IN查询。因为在第一个IN查询中只有NOT子句，所以可以将这些子句更改为NOT IN并删除子查询中的NOT。这也将删除第二个OR IN。

select * 
from rooms 
where id not in (
   select room_id 
   from reservations 
   where '2018-6-11' BETWEEN date_at and date_add(date_at, INTERVAL period day) 
   or '2018-6-30' between date_at and date_add(date_at, INTERVAL period day)
   or date_at between '2018-6-11' and '2018-6-30' 
   or date_add(date_at ,INTERVAL period day ) between '2018-6-11' and '2018-6-30' )) 

基本上，这个查询的意思是:为我提供所有没有预订的房间，我的开始或结束日期在预订时间之间。

编辑

多亏了@vivek_23，我认识到查询不能从内部条件的角度进行简化--尽管如此，这个版本还是将对数据库的查询量减少到了2。