mysql - 如何找到开始和结束几乎与时间匹配的行列表？

Question

我有一个包含一些时间存在的表格，如下所示:

presences (id, account_id, start, end, date);

这里有一些条目:

1, 1, 10:00:00, 11:00:00, 2013-07-02
2, 2, 10:05:00, 11:05:00, 2013-07-02
3, 3, 9:55:00, 11:10:00, 2013-07-02
4, 4, 10:02:00, 10:58:00, 2013-07-02
5, 1, 14:00:00, 15:30:00, 2013-07-02
6, 2, 14:03:00, 15:36:00, 2013-07-02
7, 3, 13:56:00, 15:28:00, 2013-07-02
8, 4, 14:05:00, 15:30:00, 2013-07-02

如您所见，帐户的开始和结束时间大约10-11/14-15h，但并不总是如此完全相同，因此我无法搜索 WHERE start = 14:00:00 AND end = 15:30:00 例如。

对于查询，我有单行(id:1)的日期和值，并且我喜欢 MySQL 返回具有相同日期的所有存在，是 不是{id}并且开始和结束时间在同一时间范围内

到目前为止，我已经有了这个实现，但我正在寻找更好的方法(如果存在)来匹配结果:

我得到start + (end - start/2)的中间时间(对于id:1，这意味着10:30:00)，我像这样搜索:

SELECT * 
FROM presences 
WHERE id != 1 
  AND start < 10:30:00 AND end > 10:30:00 
  AND date = 2013-07-02

id 和 date 是从查询中找到的值 (SELECT * FROM Presences WHERE id = 1)，start 和 end 的计算如前所述。

有没有更好的方法来找到相似的时间？

Answer 1

您可以在一个查询中完成此操作:

SELECT * 
FROM presences p cross join
     (select p.* from presences p where id = 1
     ) p1
where p.id <> p1.id and
      p.date = p1.date and
      p.start < p1.start + (p1.end - p1.start / 2) and
      p.end >  p1.start + (p1.end - p1.start / 2) 

如果您想要任何重叠，请使用以下逻辑:

SELECT * 
FROM presences p cross join
     (select p.* from presences p where id = 1
     ) p1
where p.id <> p1.id and
      p.date = p1.date and
      (p.start < p1.end and
       p.end > p1.start
      )

如果您想在范围内开始和结束，请明确查找:

SELECT * 
FROM presences p cross join
     (select p.* from presences p where id = 1
     ) p1
where p.id <> p1.id and
      p.date = p1.date and
      p.start between p1.start + p1.end and
      p.end between p1.start + p1.end

您可以在 p1.start 和 p1.end 中添加/减去几分钟，以使匹配更加模糊。