php - SQL 选择具有可用日期的属性

Question

这实际上是两个部分，但现在请随意只回答第 1 部分。

表格

Property
==============
ID  |  ... |


Calendar
=============
property_id |  start_date | end_date | block_reason

property 表包含有关属性的所有数据。 calendar 表包含出于某种原因被阻止的日期范围。 start_date 和 end_date 之间(包括在内)的日期被视为已阻止。 property 与 calendar 表具有一对多关系。

查询(第 1 部分)

我想选择一个可用日期介于给定开始日期和结束日期之间的属性。到目前为止，我已经尝试过了。

SELECT * 
FROM property AS p
LEFT JOIN calendar AS c ON c.property_id=p.id
WHERE (c.start_date IS NULL OR c.start_date > "{my start date}") 
  AND (c.end_date IS NULL OR c.end_date < "{my end date}")

如果该属性在日历表中没有结果，则此方法有效。但否则会非常失败(空结果集)。

查询(第 2 部分)

一旦我们开始第 1 部分的工作。用户还可以提供可用天数，我们需要选择在给定开始-结束范围内有连续可用天数的属性。基本上“我想要一个在 X 和 Y 日期之间有 N 天可用的属性。”

我整个早上都在用谷歌搜索这个，我的 friend /同事似乎也不知道从哪里开始。因为结果是分页的，所以我想避免在 PHP 中处理这个问题，而不得不执行整个选择、过滤、重复直到没有结果或整页，逻辑 shindig。

感谢您的帮助。谢谢。

编辑

根据请求，一个带有虚拟数据的 fiddle 。 http://sqlfiddle.com/#!9/854eb/7

Answer 1

想法是计算两者之间的天数，然后将其与天数进行比较。假设没有重叠并且结束日期包括在内:

SELECT c.property_id
FROM calendar c
WHERE c.start_date <= {my end date} AND
      c.end_date >= {my start date}
GROUP BY c.property_id
HAVING SUM(datediff(greatest({my start date}, c.start_date),
                    least({my end date}, c.end_date)
                   ) + 1
          ) < datediff({my end date} , {my start date}) + 1