我的解决方案是将问题分成两部分（最后是两个查询连接在一起）。可能不是最有效的，但解决方案是正确的。
1） 在单人房中，查看最后一个退房日期，看哪个房间先空置（即本月剩余时间内不再有预订）2）在当前预订之间办理入住手续-看他们之间是否有3天的间隔3）将这些房间放在一起-抓住最短时间

WITH subquery AS( -- existing single-bed bookings in Dec 
SELECT room_no, booking_date, 
        DATE_ADD(booking_date, INTERVAL (nights-1) DAY) AS last_night
   FROM booking
   WHERE room_type_requested='single' AND
         DATE_ADD(booking_date, INTERVAL (nights-1) DAY)>='2016-12-1' AND 
         booking_date <='2016-12-31'
   ORDER BY room_no, last_night) 

SELECT room_no, MIN(first_avail) AS first_avail --3) join the 2 together
FROM( 

-- 1) check the last date the room is booked in December (available after)

SELECT room_no, MIN(first_avail) AS first_avail
  FROM(
      SELECT room_no, DATE_ADD(MAX(last_night), INTERVAL 1 DAY) AS first_avail
        FROM subquery q3
        GROUP BY 1
        ORDER BY 2) AS t2

UNION 

-- 2) check if any 3-day exist in between reservations 

SELECT room_no, DATE_ADD(MIN(end2), INTERVAL 1 DAY) AS first_avail
  FROM(
      SELECT q1.booking_date AS beg1, q1.room_no, q1.last_night AS end1, 
             q2.booking_date AS beg2, q2.last_night AS end2
        FROM subquery q1
        JOIN subquery q2 
          ON q1.room_no = q2.room_no AND q2.booking_date > q1.last_night
      GROUP BY 2,1
      ORDER BY 2,1) AS t
      WHERE beg2-end1 > 3) AS inner_t

如果没有日历桌，这似乎很难做到——因为一个合适的房间在一个月内可能根本没有预订。如果没有任何预订，这个月就没有记录了。

select r.id, dte
from rooms r cross join
     (select date('2018-12-01') as dte union all
      select date('2018-12-02') as dte union all
      . . .
      select date('2018-12-32') as dte 
     ) d
where not exists (select 1 from bookings b where b.room_no = r.id and b.booking_date = d.dte) and
      not exists (select 1 from bookings b where b.room_no = r.id and b.booking_date = d.dte + interval 1 day) and
      not exists (select 1 from bookings b where b.room_no = r.id and b.booking_date = d.dte + interval 2 day)
order by d.dte
limit 1;

这是假设 booking_date 是停留的开始。你需要提供“单人房”的逻辑。

select distinct top 1 alll.i,alll.room_no,
case
when (select count(*) from booking where room_no = alll.room_no and booking_date between dateadd(day,1,alll.i) and dateadd(day,3,alll.i)) > 0  then 'Y'
else 'N'
end as av3
 from
(select c.i,b.room_no,b.booking_date
from calendar c cross join booking b
where month(c.i) = 12 and year(c.i) = 2016 and b.room_type_requested = 'single'
) as alll
join
(
select distinct c.i, b.room_no
from calendar c join booking b
on c.i between b.booking_date and DATEADD(day,b.nights-1,b.booking_date)
where month(c.i) = 12 and year(c.i) = 2016 and b.room_type_requested = 'single'
) as booked
on alll.i = booked.i
and alll.room_no <> booked.room_no
order by 1

这很管用。这有点复杂，但基本上首先检查所有已预订的房间，然后在每月的每一天对未预订的房间进行比较，直到接下来的3天。

很抱歉，我对这种查询有点生疏，我不能保证所有语法都正确无误，但我认为下面的方法可能会奏效：

SELECT id, DATE_ADD(b.booking_date, INTERVAL (end_date + 1 DAY) as date
FROM  (
         SELECT r.id, STR_TO_DATE('2016-01-01', '%Y-%m-%d') as start_of_month, b.booking_date as start_date, DATE_ADD(b.booking_date, INTERVAL (nights - 1) DAY) as end_date
         FROM room r
         LEFT JOIN booking b ON r.id = b.room_no
         ORDER BY r.id, b.booking_date
      ) as room_bookings
WHERE DATE_DIFF(room_bookings.start_of_month, room_bookings.start_date) >= 3
OR DATE_DIFF(room_bookings.end_date, (
         SELECT b2.booking_date FROM booking b2
         WHERE b2.room_no = room_bookings.id AND b2.booking_date > room_bookings.start_date
         ORDER BY b2.booking_date LIMIT 1)
      ) >= 3

事实上，现在我把这些都打出来了，您也许可以调整主查询的where，这样您甚至不需要room\u bookings子选择。希望这会有所帮助，而且不会太离题。

这在概念上是有效的，因为第一个可用日期应该始终是前一个预订的结束日期。

SELECT MIN(DATE_ADD(a.booking_date, INTERVAL nights DAY)) AS i
FROM booking AS a
WHERE DATE_ADD(a.booking_date, INTERVAL nights DAY)
      >= '2016-12-01'
      AND room_type_requested = 'single'
      AND NOT EXISTS
      (SELECT 1 FROM booking AS b
        WHERE b.booking_date BETWEEN
        DATE_ADD(a.booking_date, INTERVAL nights DAY) 
        AND DATE_ADD(a.booking_date, INTERVAL nights+2 DAY)
        AND a.room_no = b.room_no)