mysql - mysql 中多家酒店的房间可用性

Question

我正在尝试创建一个在线房间预订系统。

数据库的一个表应该保存预订。它有一个自动编号字段、客户数据字段、两个到达和离开的日期字段以及一个用于预订房间数量的数字字段。

搜索页面将到达和离开日期提交到结果页面，然后结果页面应该告诉客户该期间内有多少房间可用(如果有)。这就是一切出错的地方。我只是无法准确计算在请求的时间内已预订的房间数量。

guest  | arrive     | depart      |booked |room_idSmith  | 2017-06-20 | 2017-06-22  | 3     |1Jones  | 2017-06-20 | 2017-06-21  | 2     |1Brown  | 2017-06-22 | 2017-06-23  | 3     |2White  | 2017-06-15 | 2017-06-20  | 4     |1

房间ID来自

id     |  type       | count   |hotelid1      | Single room |  5      | 12      | Deluxe room |  8      | 1

如果 roomid 1 有 5 个房间，2 有 8 个房间，我想要这样的结果。

date        available     roomid2017-06-20  |   0      |  12017-06-20  |   8      |  22017-06-21  |   2      |  12017-06-21  |   8      |  22017-06-22  |   5      |  12017-06-22  |   5      |  2

我也在使用日历表。请帮我找到解决办法

Answer 1

下面的查询怎么样？它返回每种房间类型和日期的房间类型、日期、可用房间(即该类型的房间总数，而不是剩余房间)和预订的房间。

select b.roomid, c.dt, sum(r.count) as total_rooms, 
  coalesce(sum(b.booking_cnt), 0) as booked_rooms
from calendar_table as c
left join bookinglist b on c.dt between b.date_from and b.date_to
left join rooms as r on b.roomid = r.id
where c.dt between DATE_START and DATE_END
group by b.roomid, c.dt
order by b.roomid, c.dt

然后在前端，可以减去total_rooms和booked_rooms列以获得可用房间数。

请记住，我没有架构或数据来测试此查询，但它应该可以让您了解下一步该去哪里。下次您可以创建SQL Fiddle以便更轻松地创建答案。