如何高效地查找所有与日期区间没有重叠的记录？

Question

假设我有一张hotel_rooms的表，每个房间都有一些相关的预订记录。如下所示：

hotel_rooms:

hotel_room_id | hotel_name -------------------------- 1 | Hotel A 2 | Hotel B 3 | Hotel C

reservations:

reservation_id | hotel_room_id | start_date | end_date ------------------------------------------------------------ 1 | 1 | 2011年6月1日 | 2011年6月10日 2 | 1 | 2011年6月20日 | 2011年6月30日 3 | 2 | 2011年6月11日 | 2011年6月15日 4 | 3 | 2011年6月1日 | 2011年6月4日

那么我该如何找到所有的hotel_rooms，在入住日期和离店日期没有与任何hotel_rooms的预订重叠的情况下呢？例如，如果入住日期是2011年6月4日，退房日期是2011年6月8日，那么只会显示Hotel B，而不会显示Hotel A和Hotel C。如果入住日期是2011年6月16日，退房日期是2011年6月19日，则三个酒店都会显示出来。

虽然有多种方法可以解决这个问题，但对于我们的情况，假设hotel_rooms中有很多记录，每个房间都与许多预订记录相关联。 那么怎样才能高效地处理这个搜索呢？对于我的目的，我使用MySQL和PHP（但我不一定需要针对任何一个解决方案）。

- jameslk

---

你的作业？为什么要重新做hotels.com？ - Nik

不是作业。这是为我正在构建的一个网站，实际上与酒店无关。在这种情况下，酒店似乎是使用最清晰的例子。 - jameslk

我急切地等待OMG Ponies（又名SQL King）对此的回复。 - Jason

看看这个答案。我认为它做了你想要的事情。http://stackoverflow.com/questions/5002689/how-can-i-determine-in-sql-server-if-a-datetime-range-overlaps-another/5002738#5002738 - Mikael Eriksson

Answer 1

SELECT h.*
FROM hotel_rooms h
  LEFT JOIN reservations r
    ON h.hotel_room_id = r.hotel_room_id
   AND checkin_date <= r.end_date
   AND checkout_date >= r.start_date
WHERE r.reservation_id IS NULL

或者：

SELECT *
FROM hotel_rooms h
  WHERE NOT EXISTS (
    SELECT *
    FROM reservations r
    WHERE h.hotel_room_id = r.hotel_room_id
      AND checkin_date <= r.end_date
      AND checkout_date >= r.start_date
  )

- Andriy M

Answer 2

我认为使用一些NOT EXISTS子查询对你有帮助。假设表格被正确地索引，时间应该不是问题。

SELECT *
FROM hotel_rooms
WHERE NOT EXISTS (SELECT 1 FROM reservations 
                  WHERE reservations.hotel_room_id = hotel_rooms.hotel_room_id
                  AND checkin_date BETWEEN start_date AND end_date)
AND NOT EXISTS (SELECT 1 FROM reservations 
                WHERE reservations.hotel_room_id = hotel_rooms.hotel_room_id 
                AND checkout_date BETWEEN start_date AND end_date)

- dpmattingly

---

我认为您忽略了单个预订的情况，即 checkin_date < start_date < end_date < checkout_date。相反，我建议同时进行两个检查 check_in_date<end_date and check_out_date>start_date。 - Howard