查询下一个时间间隔 [英] Query the Next Time Interval

问题描述

我试图从mySQL表查询上一个可用的时间范围

Im trying to query the upper available "time range" from a mySQL table

+----+----------+---------+
| 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

the query result would be the next available time range which is 10:30 to 15:00

edit1：id不在

edit1: id is not in sequence

谢谢！

推荐答案

我想你需要这样： / p>

I think you need this:

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`)

（只有间隔不重叠的情况下才有效）

(this will work only if intervals don't overlap)

这篇关于查询下一个时间间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！