SQL检查一组日期是否在指定的日期范围内 [英] SQL To check if a set of dates fall within a specified range of dates

问题描述

伙计们，我有一个表，其中包含模式中没有房间的日期:

Hi guys I have a table which holds dates when a room is unavailable in the schema:

ROOM_ID | DATE_UNAVAILABLE

我需要一个SQL查询来检查是否有房间在两个日期之间的时间可用-沿...

I need a sql query that checks if a room is available during between a range of two dates - some thing along the line of

Select All rooms that are constantly available between date 1 and date 2

或更确切地说

选择所有没有在日期不可用表中输入日期的房间，该日期在日期1和日期2之间

select all rooms that don not have a date entered in the date unavailable table which falls between date 1 and date 2

我在这里使用php MySQL

I'm using php MySQL here

谢谢

推荐答案

内部查询找到不可用的房间，然后我们使用不存在"左联接从结果中删除这些日期，从而为我们提供可用的房间

The inner query finds the room that are not available, then we use a Not-exists left join to remove those dates from our results, leaving us with available rooms only.

SELECT r.ROOM_ID
FROM rooms r LEFT JOIN (
    SELECT ROOM_ID
    FROM tableName
    WHERE DATE_UNAVAILABLE BETWEEN 'Date1' AND 'Date2'
    GROUP BY ROOM_ID
  ) g ON r.ROOM_ID = g.ROOM_ID
WHERE g.ROOM_ID IS NULL

或者，如果您有正确的索引，则跳过组的速度可能会更快:

Alternativly, if you have correct indexes in place, skipping the group may be faster:

SELECT r.ROOM_ID
FROM rooms r LEFT JOIN tableName g ON r.ROOM_ID = g.ROOM_ID
    AND g.DATE_UNAVAILABLE BETWEEN 'Date1' AND 'Date2'
WHERE g.ROOM_ID IS NULL

这篇关于SQL检查一组日期是否在指定的日期范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！