mysql - 记录之间的平均间隔

Question

编辑:我有一个包含开始和结束时间字段的表。我会将公共(public)时间之间的间隔分组。这可能吗？

假设这个表:

time_begin time_end
07:00      18:00
09:00      12:00 (this interval is in the first line)
17:00      18:00 (this interval is in the first line)

我想返回这些行:

time_begin time_end
09:00      12:00
17:00      18:00

谢谢

Answer 1

尝试这个查询。

SELECT time_begin, time_end
FROM table_name
WHERE time_begin < (SELECT time_end 
                    FROM table_name
                    LIMIT 1)
AND   time_begin > (SELECT time_begin
                    FROM table_name
                    LIMIT 1)
AND   time_end < (SELECT time_end
                    FROM table_name
                    LIMIT 1)
AND   time_end > (SELECT time_begin
                    FROM table_name
                    LIMIT 1)

此查询返回第一行时间间隔之间的所有时间间隔。