mysql 判断日期是否在某范围内,SQL检查一组日期是否在指定的日期范围内

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

ROOM_ID | DATE_UNAVAILABLE

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

or rather

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

I'm using php MySQL here

Thanks

解决方案

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