背景/应用

我有一个 MySQL 数据库，其中包含可出租特性表和这些特性的预订表。还有一个搜索功能，用于查找两个提供日期之间的可用属性。搜索时，用户可以输入开始日期、他们希望停留的天数以及最多 +/- 7 天的日期灵 active 。预订可以在另一预订结束的同一天开始(第 1 方早上离开，第 2 方晚上到达)。

我很难有效地实现灵 active 功能。

架构

CREATE TABLE IF NOT EXISTS `property` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `property_booking` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT,
    `property_id` bigint(20) DEFAULT NULL,
    `name` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
    `date_start` date DEFAULT NULL,
    `date_end` date DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

示例数据

INSERT INTO `property` (`name`) 
VALUES ('Property 1'), ('Property 2'), ('Property 3');

INSERT INTO `property_booking` (`property_id`,`name`,`date_start`,`date_end`) 
VALUES (1, 'Steve', '2011-03-01', '2011-03-08'), 
(2, 'Bob', '2011-03-13', '2011-03-20'), 
(3, 'Jim', '2011-03-16', '2011-03-23');

示例场景

用户选择他们想在 2011 年 3 月 10 日开始他们的逗留，他们想要逗留 7 天，并且他们有 +/- 2 天的灵 active 。我已经编译了一个图像来可视化下面的数据和参数。 (红色:预订 1，绿色:预订 2，条纹:预订 3，蓝色:日期范围(2011 年 3 月 10 日，+ 7 天和 +/- 2 天的灵 active ))

预期结果

酒店 1 (在整个日期范围内均可预订)
属性(property) 3(可从 2011-03-08 或 2011-03-09 开始预订)

当前方法

我当前的查询检查整个可搜索日期范围内的所有 7 天日期范围是否重叠，如下所示:

SELECT p.`id`, p.`name` 
FROM `property` p 
WHERE (NOT (EXISTS (SELECT p2.`name` FROM `property_booking` p2 WHERE (p2.`property_id` = p.`id` AND '2011-03-10' < DATE_SUB(p2.`date_end`, INTERVAL 1 DAY) AND '2011-03-17' > DATE_ADD(p2.`date_start`, INTERVAL 1 DAY))))) 
OR (NOT (EXISTS (SELECT p3.`name` FROM `property_booking` p3 WHERE (p3.`property_id` = p.`id` AND '2011-03-11' < DATE_SUB(p3.`date_end`, INTERVAL 1 DAY) AND '2011-03-18' > DATE_ADD(p3.`date_start`, INTERVAL 1 DAY))))) 
OR (NOT (EXISTS (SELECT p4.`name` FROM `property_booking` p4 WHERE (p4.`property_id` = p.`id` AND '2011-03-09' < DATE_SUB(p4.`date_end`, INTERVAL 1 DAY) AND '2011-03-16' > DATE_ADD(p4.`date_start`, INTERVAL 1 DAY))))) 
OR (NOT (EXISTS (SELECT p5.`name` FROM `property_booking` p5 WHERE (p5.`property_id` = p.`id` AND '2011-03-12' < DATE_SUB(p5.`date_end`, INTERVAL 1 DAY) AND '2011-03-19' > DATE_ADD(p5.`date_start`, INTERVAL 1 DAY)))))
OR (NOT (EXISTS (SELECT p6.`name` FROM `property_booking` p6 WHERE (p6.`property_id` = p.`id` AND '2011-03-08' < DATE_SUB(p6.`date_end`, INTERVAL 1 DAY) AND '2011-03-15' > DATE_ADD(p6.`date_start`, INTERVAL 1 DAY)))));

在示例数据集上，它相当快，但在更大的数据集上，它会变得非常缓慢，当您构建完整的 +/- 7 天灵 active 时更是如此。

对于如何更好地编写此查询，有没有人有任何建议？

最佳答案

好的，这是一个棘手问题的棘手答案...

SELECT * FROM property AS p
LEFT JOIN  
(
  SELECT property_id, DATEDIFF(MAX(date_end),20110308) AS startblock, 
      DATEDIFF(20110319,MIN(date_start))-1 AS endblock
  FROM property_booking AS pb
  WHERE date_start < 20110319 || date_end >= 20110308 
  GROUP BY property_id
  HAVING LEAST(startblock,endblock) > 4
) AS p2 ON p.id = p2.property_id 
WHERE p2.property_id IS NULL;

子查询选择所有不符合条件的属性。 IS NULL 的 LEFT JOIN 基本上可以排除(对不合格属性的否定)

• 20110308 是所需的开始日期 -2 天(因为 +/-2 天的灵 active )
• 20110319 是期望的结束日期 +2 天
• HAVING LEAST(startblock,endblock) > 4 中的数字 4 是 +/- 数字 (2*2) 的两倍

我花了一段时间才解决(但你的问题很有趣，我有时间)

我已经用边缘案例对其进行了测试，并且它适用于我抛出的所有测试用例......)。其背后的逻辑有点奇怪，但一个好的旧笔和纸帮助我解决了这个问题!

编辑

不幸的是，我意识到这适用于大多数情况，但并非全部...(在查询期的开始和结束时进行 2 天的单日预订，即使该属性(property)应该可用，也无法使用)。

这里的问题是您必须查找数据库中不“存在”的信息，并根据您拥有的数据重建它。查看我对您问题的评论，了解解决问题的更好方法

关于MySQL:匹配在两个日期之间有 x 个连续日期可用的记录，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/5031388/