查找重叠的日期给无限循环MySQL [英] Find Overlapping Dates Give Infinite Loop MySQL

问题描述

假设我有一张给定日期的桌子:

Let's assume I have one table with given dates:

+-----------+-----------+----+
|StartDate  |EndDate    |ID  |
+-----------+-----------+----+
|2013-08-29 |2014-12-29 |1   |
|2013-08-29 |2013-09-31 |2   |
|2015-01-02 |2015-03-20 |3   |
+-----------+-----------+----+

我想找到冲突的日期，因此代码应给我1作为冲突的日期.当我用MySQL编写此代码时，它会进入一个无限循环，我将不胜感激，因为我无法理解为什么它不起作用:

I want to find the conflicting dates, so the code should give me 1 as the conflicting dates. When I write this code in MySQL, it enters an infinite loop, which I would appreciate a little help, because I cannot see why it does not work:

select t1.* from dates t1
inner join dates t2
on t2.StartDate > t1.StartDate
and t2.StartDate < t1.EndDate;

谢谢.

推荐答案

不是吗?:

select t1.*, t2.id as conflict_id
from dates t1
inner join dates t2
on t2.StartDate >= t1.StartDate
and t2.EndDate <= t1.EndDate
and t2.id != t1.id

这篇关于查找重叠的日期给无限循环MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！