需要查看日期范围是否与sql中的另一个日期范围重叠 [英] Need to see if a range of dates overlaps another range of dates in sql

问题描述

我有一个表，用于存储房间的预订，架构为:

I have a table which stores bookings of rooms, the schema is:

ID | ROOM_ID | CHECK_IN_DATE | CHECK_OUT_DATE | USER_ID

我需要对在指定日期范围内可用/不可用的房间进行搜索查询.

I need to run a search query for rooms which are available/unavailable between a set range of dates.

还请记住，存在另一个表，该表保留了预订房间时的日期，其格式为:

Also keep in mind that there exists another table which holds dates when the room is prebooked and its in the format:

ROOM_ID | DATE

因此，我需要运行一个查询，以查找在设定范围内可用的房间，我将如何制定查询条件? 我在这里使用MySQL.

SO I need to run a query which looks for rooms available within a set range, How would I formulate the query? I'm using MySQL here.

-编辑---

还有该模式的房间"表:

Theres also a Rooms table of the schema:

ID | ROOM DETAILS etc

不可用/预定日期"表基本上只包含零星的单个日期，不可用/预定日期"表中的每个日期指的是出于某种原因而无法预定房间的日期，例如:维护等

The unavailability/prebooked dates table basically holds sporadic single dates, each date in the unavailability table refers to a date when the room for some reason cannot be booked eg: maintenance etc

推荐答案

SELECT
   ROOM_ID
FROM
   Rooms r
   LEFT JOIN Bookings b ON (
      r.ROOM_ID = b.ROOM_ID
      AND b.CHECK_IN_DATE > '$MAX_DATE'
      AND b.CHECK_OUT_DATE < '$MIN_DATE'
   )

我不确定预定房间的因素，因为没有日期范围.预订房间是否还会获得预订条目?

I'm not sure how pre-booked rooms factors in as there is no date range. Do pre-booked rooms also get an entry on bookings or not?

这篇关于需要查看日期范围是否与sql中的另一个日期范围重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！