mysql - 查询下一次间隔

我试图从mySQL表中查询可用的“时间范围”上限

+----+----------+---------+
| id | timefrom | timeto  |
+----+----------+---------+
|  0 | 08:30    | 10:30   |
|  7 | 15:00    | 16:00   |
|  2 | 17:00    | 17:30   |
|  8 | 18:00    | 21:00   |
+----+----------+---------+

查询结果将是下一个可用的时间范围，即10:30到15:00
edit1:id不在序列中
谢谢！

最佳答案

我想你需要这个：

select t1.`timeto`, min(t2.`timefrom`)
from
  yourtable t1 inner join yourtable t2
  on t1.`timeto`<t2.`timefrom`
group by t1.`timeto`
having
  not exists (select null from yourtable t3
              where t1.`timeto`<t3.`timeto`
              and min(t2.`timefrom`)>t3.`timefrom`)

（仅当间隔不重叠时，此操作才起作用）