mysql查询某个日期范围_MySQL查询某些日期范围

令人惊讶的是,近两年没有人注意到这一点,但其他答案都是错误的,因为他们没有考虑到开始日期和结束日期都超出搜索范围范围的情况.考虑这是日期的范围：

start_date <> end_date

这是我们搜索的范围：

start_date <> end_date

start_search <> end_search

搜索应该给我们一个积极的结果,因为它们相交.但是如果您使用其他答案,则会得到否定结果,因为start_date和end_date都不在start_search和end_search之间.

为了得到解决方案,让我们绘制所有4种可能的交叉模式：

start_date <> end_date

start_search <> end_search

start_date <> end_date

start_search <> end_search

start_date <> end_date

start_search <> end_search

start_date <> end_date

start_search <> end_search

您可以或所有4种可能的案例来获得直接的解决方案：

select*from table where

/* 1st case */ start_date between start_search and end_search

or /* 2nd case */ end_date between start_search and end_search

or /* 3rd case */ (start_date <= start_search and end_date >= end_search)

or /* 4th case */ (start_date >= start_search and end_date <= end_search)

/* the 4th case here is actually redundant since it is being covered by the 1st and 2nd cases */

一个不太直接的解决方案是：

select*from table where

start_date between start_search and end_search /* covers 1st and 4th cases */

or start_search between start_date and end_date /* covers 2nd and 3rd cases */

尝试使用上面的图表将其可视化.

如果我们尝试从上面的4个图中推断出一个模式,我们可以看到在交集期间,end_date总是> = start_search,而另一方面,start_date总是< = end_search.事实上,进一步可视化,我们可以看到,当这两个条件成立时,我们不能没有交叉点. 因此,另一种解决方案很简单：

select*from table where

end_date >= start_search && start_date <= end_search

此解决方案的优点是我们只需要进行2次比较.与“OR OR”方法形成对比,该方法需要2到8(3& plus; 3& plus; 2)比较. (每次通话之间由3 comparisons组成.)