在两个日期之间选择可用的房间 [英] Select available rooms between two dates

问题描述

我上面有可用的日期和价格的mysql表.第二个表包括房间的详细信息.如何连接两个表以获取两个日期之间的可用房间，而不会得到重复的内容.

I have above mysql table with available dates and prices. Second table includes room details. How can I join two tables to get available rooms between two dates and not get duplicate content.

推荐答案

在这里很难为您提供完整的答案，因为您只向我们显示包含预订的表格-我们不知道该预订的范围有空房间.

This is hard to come up with a complete answer for you here, as you are only showing us the table which contains the bookings - we cannot know what range of rooms are available.

返回至少在选定时间段内已预订的房间的room_id的SQL可能是:

SQL which returns the room_id's for rooms which are booked for at least part of the selected period could be:

SELECT `room_id` , COUNT(*)
FROM `bookings`
WHERE `dt` BETWEEN "[start date]" AND "[end date]"
GROUP BY `room_id`;

如果您有房间表(而不是预订表)，则可以通过以下方式返回该期间未预订的任何房间的列表:

If you had a table of rooms (rather than bookings), it would be possible for you to return a list of any rooms not booked during that period with:

SELECT `id`
FROM `rooms`
WHERE `id` NOT IN (
  SELECT DISTINCT( `room_id` )
  FROM `bookings`
  WHERE `dt` BETWEEN "[start date]" AND "[end date]"
);

修正

基于OP的反馈，现在的假设为:

AMENDMENT

Based on the feedback by OP, the assumptions are now:

• 该表包含可用于在dt列中的日期至第二天结束的期间内的客房的详细信息(即酒店客房)
• 查询应返回在输入期间内所有可用的房间(因此，将仅返回从DAY A到DAY B可用的房间.

• The table contains details of rooms which are available for a period starting on the date in column dt and ending the following day (ie hotel rooms)
• The query should return any rooms which are available for the entirity of the period entered (so only rooms which are available from DAY A to DAY B will be returned.

因此，修改后的代码为:

As such, the amended code is:

SELECT room_id
FROM available_rooms
WHERE dt BETWEEN "[start date]" AND DATE_SUB("[end date]",INTERVAL 1 DAY)
GROUP BY room_id
HAVING COUNT(*)=ABS(DATEDIFF("[start date]","[end date]"));

这篇关于在两个日期之间选择可用的房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！