重叠时间间隔

Question

给出以下表格，我想要发现2个或多个用户之间重叠发生的时间间隔，并记录涉及重叠的用户以及时间。我不知道该如何处理，所以任何帮助都将不胜感激。

Table1:

UserID  StartDate   EndDate
1       15:00       22:00
2       12:00       18:00
3       02:00       09:00
4       07:00       13:00

Expected Result:

StartDate       EndDate     Users
15:00           18:00       1,2
07:00           09:00       3,4
12:00           13:00       2,4

- user3309825

---

这些可能会有用：6571538/checking-a-table-for-time-overlap。另外：flipflops.org/2008/05/01/finding-overlapping-dates-and-times-in-mysql。 - Ryan Vincent

Answer 1

这是在SQL Fiddle中的代码。

如果您需要解释：

1. I joined the table twice on those rows that has overlapping:

   from over t1 join over t2 on t1.START < t2.END and t1.START > t2.START
   

2. then selected simply the highest start and lowest end dates:

   greatest(t1.START, t2.START), least(t1.END, t2.END)
   

3. ... and in a nested select grouped the ids of those rows that covers the iterval:

   t3.START <= greatest(t1.START, t2.START) and t3.END >= least(t1.END, t2.END)
   

- cy3er