如何检索覆盖指定日期区间全部天数的可用租赁/酒店单元记录？

针对你提到的度假租赁/酒店场景，我整理了一套实操性强的方案，帮你搞定「按指定日期检索可用单元」和「日/周价拆分计算」的核心需求：

核心需求拆解 #

• 仅返回完全覆盖入住-退房时间段且全程可用的单元（哪怕有一天不可用都直接排除）
• 支持单日预订的按日计价，同时周价仅在预订区间为完整7天且该周全程可用时生效

数据库表设计建议 #

单元基础表 (units) #

存储每个单元的静态信息：

• unit_id (主键，唯一标识单元)
• unit_name (比如「海景双人房」「山景独栋别墅」)
• max_guests (最大容纳人数)
• amenities (配套设施，可存JSON格式或关联子表)

日维度价格与可用表 (daily_pricing_availability) #

这是核心业务表，按「单元+日期」维度存储动态数据：

• unit_id (外键关联units表)
• date (日期，格式如2024-06-01)
• daily_price (当日单价，NULL表示该日不可用)
• weekly_price (仅在周起始日（比如周一）的记录中存储，代表整周预订的优惠价)
• is_available (布尔值，和daily_price非空形成双校验，避免数据不一致)

可用单元检索逻辑 #

假设用户输入的入住日期为check_in，退房日期为check_out（行业惯例：退房日当天不住，实际覆盖的日期区间是[check_in, check_out)）

方案1：用NOT EXISTS排除不可用单元 #

逻辑清晰，适合中小规模数据：

SELECT u.unit_id, u.unit_name
FROM units u
WHERE NOT EXISTS (
    SELECT 1
    FROM daily_pricing_availability dpa
    WHERE dpa.unit_id = u.unit_id
      AND dpa.date >= '2024-06-01' -- 入住日期
      AND dpa.date < '2024-06-05'  -- 退房日期
      AND dpa.is_available = FALSE
);

方案2：用GROUP BY校验全时段可用 #

适合需要同时统计总价的场景：

SELECT u.unit_id, u.unit_name, SUM(dpa.daily_price) AS total_daily_price
FROM units u
JOIN daily_pricing_availability dpa ON u.unit_id = dpa.unit_id
WHERE dpa.date >= '2024-06-01'
  AND dpa.date < '2024-06-05'
GROUP BY u.unit_id, u.unit_name
HAVING COUNT(CASE WHEN dpa.is_available = TRUE THEN 1 END) = DATEDIFF('2024-06-05', '2024-06-01');

价格计算逻辑 #

单日预订 #

直接取对应日期的daily_price即可：

SELECT daily_price
FROM daily_pricing_availability
WHERE unit_id = 'U123'
  AND date = '2024-06-01';

周价生效判断 #

假设周起始日为周一，只有当预订区间恰好是7天，且该周起始日存在weekly_price、整周所有日期都可用时，才使用周价：

SELECT 
    CASE
        -- 校验整周可用且周价存在
        WHEN COUNT(CASE WHEN is_available = TRUE THEN 1 END) = 7
             AND (SELECT weekly_price FROM daily_pricing_availability WHERE unit_id='U123' AND date='2024-06-03') IS NOT NULL
        THEN (SELECT weekly_price FROM daily_pricing_availability WHERE unit_id='U123' AND date='2024-06-03')
        -- 否则按日价总和计算
        ELSE SUM(daily_price)
    END AS final_total_price
FROM daily_pricing_availability
WHERE unit_id = 'U123'
  AND date >= '2024-06-03'
  AND date < '2024-06-10';

性能优化小技巧 #

• 给daily_pricing_availability表建立复合索引：(unit_id, date, is_available)，能大幅加快日期区间检索的速度
• 对热门日期（比如节假日）提前缓存可用单元列表，减少数据库实时查询压力
• 如果支持多周预订，可以批量按周校验区间，避免逐天检查的冗余操作

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