时间戳

首页 > 解决方案 > 我怎样才能简化这个关于预订的查询？

php - 我怎样才能简化这个关于预订的查询？

问题描述

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

预订表包含 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)

标签： phpmysql

解决方案

将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;

推荐阅读