酒店APP指定日期区间可用客房的SQL查询需求

我正在开发一款酒店APP，需要编写SQL语句查询数据库中指定日期区间内的可用客房。

rate表结构及数据 #

筛选条件 #

• 在from = 16/08/2022至to = 18/08/2022的每一天都有至少一条对应记录
• 这些记录中不存在unavailable = true的情况

预期输出 #

解决方案SQL语句 #

SELECT room_id
FROM rate
WHERE date BETWEEN STR_TO_DATE('16/08/2022', '%d/%m/%Y') AND STR_TO_DATE('18/08/2022', '%d/%m/%Y')
  AND (unavailable IS NULL OR unavailable = false)
GROUP BY room_id
HAVING COUNT(DISTINCT date) = 3;

思路说明 #

1. 日期格式转换与区间筛选：用STR_TO_DATE将字符串日期转为数据库可识别的日期类型，确保区间范围匹配准确。
2. 排除不可用记录：通过(unavailable IS NULL OR unavailable = false)过滤掉标记为不可用的记录，同时保留默认可用的null值记录。
3. 验证每日记录存在：按room_id分组后，用COUNT(DISTINCT date) = 3确认该客房在指定的3天内每天都有有效记录，满足日期覆盖要求。

内容的提问来源于stack exchange，提问作者Quentin Gaultier