MYSQL Reservation 查询，查询两个日期之间的每一天

Question

我正在尝试将其合并为一个查询。

预订系统有2个表，酒店和预订。

hotels(id, num_rooms)
reservations(hotel_id, start_date, end_date) (each reservation is for 1 room)

我需要执行一个查询，其中用户说他们在 date1 和 date2 之间需要 x 个房间，系统返回所有有足够房间的酒店(同时计算每天的所有预订以确定可用房间)的日期。

我的实验一团糟，因为我想不出如何将这些子结果组合在一起以取得一些进展。我知道我将不得不使用一些子查询来实现这一点，但我无法正确地解决这个问题。

   -- a poor snippet to try and get some foothold
   SELECT h.* from hotels as h 
   left join reservations as r on r.hotel_id=h.id 
   where 
   --(select count(*) from reservations where start date between '2015-01-01' and '2015-01-31' group by hotel_id)

Answer 1

此解决方案生成所有预订的日期范围，并计算每个酒店和日期的预订数量。使用此信息，查询然后检查房间总数减去请求期间任何一天的最大预订量是否等于或大于请求的房间数。

我使用派生表来帮助生成日期范围，但可以很容易地用数字表替换它。

我的脚本(我使用变量来限制查询):

set @start_date := '2015-04-01'; 
set @end_date   := '2015-04-04'; 
set @num_rooms  := 1;

SELECT 
    id AS hotel_id
FROM
    hotels h
        LEFT JOIN
    (SELECT 
        hotel_id, date, COUNT(*) reserved
    FROM
        (SELECT 
        r.hotel_id, a.date, r.start_date, r.end_date
    FROM
        (SELECT 
        CAST(@start_date + INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY
                AS DATETIME) AS Date
    FROM
        (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c) a
    INNER JOIN reservations r ON a.Date >= @start_date
        AND a.Date <= @end_date
    LEFT JOIN hotels h ON h.id = r.hotel_id) reservations
    WHERE
        date >= start_date AND date <= end_date
    GROUP BY hotel_id , date) a ON a.hotel_id = h.id
GROUP BY hotel_id , num_rooms
HAVING (num_rooms - MAX(reserved)) >= @num_rooms
    OR hotel_id IS NULL

我的测试数据:

create table hotels (id int, num_rooms int);
insert hotels values (1, 3),(2, 5), (3, 20);
create table reservations (hotel_id int, start_date date, end_date date);
insert reservations (hotel_id, start_date, end_date) values
(1, '2015-03-01', '2015-03-05'),
(1, '2015-03-01', '2015-03-02'),
(1, '2015-03-01', '2015-03-05'),

(2, '2015-03-01', '2015-03-05'),
(2, '2015-03-01', '2015-03-05'),
(2, '2015-03-01', '2015-03-05'),
(2, '2015-03-02', '2015-03-05'),

(3, '2015-03-01', '2015-03-05'),
(3, '2015-03-02', '2015-03-05'),
(3, '2015-03-03', '2015-03-05'),
(3, '2015-03-04', '2015-03-06');

使用上面的测试数据，查询将返回酒店 2 和 3。