mysql - 帮助使用 SQL 查询查找预订系统的下一个可用日期

Question

我正在尝试为预订系统编写一个 SQL 查询，以查找某个项目的最早可用租赁日期。

项目在数据库中按 SKU 分类。一个项目可以有多个副本，每个副本都由其序列号在数据库中唯一标识。

在搜索元素的最早可用租赁日期时，选择哪个序列号并不重要；只是下一个可用的。

数据库有2个表； “预订”和“项目”。还有一个包含数千个 YYYY-MM-DD future 日期的日历表可供使用。

Reservations 表包含列； “record_number”、“sku”、“serial_number”、“start_date”、“end_date”加上客户数据。这是每次预订时记录下来的地方。

项目表包含列； “sku”和“serial_number”。这是系统中所有租赁元素的 list 。

我已经解决了 2 天多的问题，但我的 SQL 知识不足以解决这个难题。

我已经取得了进展，已生成至少对特定 SKU 有一个预订的日期列表:

   SELECT calendar.dt
     FROM calendar
LEFT JOIN reservations ON calendar.dt >= reservations.start_date 
                      AND calendar.dt <= reservations.end_date
    WHERE reservations.sku = 'ABC123'

我可以将上述内容子查询到“NOT IN ...”选择语句中，但这只能完成查找对特定 SKU 没有预订的日期。我需要找到至少有一件商品可用的第一个日期。

我设想将日历表的日期与项目表中的 SKU 以及预订表的预订号连接起来，在预订记录中查找“NULL”，表明该日期和序列号组合不存在预订。但是我一直无法编写这样一个有效的查询。

欢迎提问。

Answer 1

以下内容应该可以让您继续前进。您可能想要调整我的“Current_Date()”函数示例，以适应您的预订开始日期和外出这么多天....

这在查询中使用了 MySQL 内联变量。内部查询是基于某个开始日期 (current_date()) 的预留 (@r) 变量的简单准备，并连接到项目表。通过不执行连接子句，否则它会为每个项目获取一个日期。在我的场景中，我只考虑外出 30 天，所以我应用了前 30 件元素的限制。除了给我足够的记录之外没有其他基础，所以我不必创建一个包含 30 条记录的临时表(或者你想出去多少天)。这将创建一个别名查询“JustDates”并具有一个列“OpenDate”。这是要测试的日期范围的基础。

这现在已加入项目表，但没有条件为每个日期创建笛卡尔，与每个项目进行比较...根据 WHERE 子句，我只关心 SKU 为“ABC123”的项目天气有 10 个序列号或 100 个。这现在可能给我 300 或 3000 个(10 个序列项 @ 30 天，或 100 个序列项 @ 30 天。

既然我有了所有单独序列号的“范围”和检查可用性的可能天数，我现在可以查询预订系统。因此，通过子选择，而不是对于给定的匹配 SKU、序列号和在预订中找到的可能日期，我只想保留未找到给定 OpenDate 的那些。我已经模拟了您的表结构并放入了一些项目、多个序列号和交错的预订日期范围，效果很好...

显然，我会确保 sku/serial 上的索引以提高性能。我可能会做的唯一额外更改是在查询预订时，排除结束日期早于您查询的开始日期的任何预订，并且可以选择没有开始日期 > 您正在考虑的最后日期。如果您有大量跨年的预订，谁会在乎相关日期范围内的古老事物或 future 的事物。

select  items.sku,
        items.serial_number,
        JustDates.OpenDate
    from 
        ( SELECT 
                 @r:= date_add(@r, interval 1 day ) OpenDate
            FROM 
                 (select @r := current_date()) vars,
                items limit 30 ) JustDates,
        items
    where 
            sku = "ABC123"
        and sku not in ( select sku from Reservations
                            where items.sku = reservations.sku
                              and items.serial_number = reservations.serial_number
                              and justDates.OpenDate >= reservations.start_date
                              and justDates.OpenDate <= reservations.end_date )
    order by 
       items.serial_number,
       justDates.OpenDate