合并重叠的时间间隔,如何? [英] Merge overlapping time intervals, how?
问题描述
有人可以帮助我进行**合并数据间隔的查询吗?
例如:普通选择:
Anyone could help me in a query that **merge a interval of datas?
ex: Common select:
选择ID,date_start,date_end从甚至到ORDER BY date_start
SELECT id, date_start, date_end FROM evento ORDER BY date_start
我知道了:
来自此:
但想要查询返回以下内容:
I GOT THIS:
FROM THIS:
but a want a query that return this:
01-2013-10-11 08:00:00 2013-10-11 15:00:00
02-2013-10-11 16:00:00 2013-10-11 19:00:00
01 - 2013-10-11 08:00:00 2013-10-11 15:00:00
02 - 2013-10-11 16:00:00 2013-10-11 19:00:00
非常感谢!
推荐答案
您也可以尝试执行此查询(除了上述注释中的PM 77-1
给出的解决方案以外,还有其他解决方案):
You may also try this query (once more solutions beside those given by PM 77-1
in the comment above) :
WITH RECURSIVE cte( id, date_start, date_end ) AS
(
SELECT id, date_start, date_end
FROM evento
UNION
SELECT e.id,
least( c.date_start, e.date_start ),
greatest( c.date_end, e.date_end )
FROM cte c
JOIN evento e
ON e.date_start between c.date_start and c.date_end
OR
e.date_end between c.date_start and c.date_end
)
SELECT distinct date_start, date_end
FROM (
SELECT id,
min( date_start) date_start,
max( date_end ) date_end
FROM cte
GROUP BY id
) xx
ORDER BY date_start;
演示---> http://www.sqlfiddle.com/#!12 /bdf7e/9
但是,对于巨大的表,此查询的性能可能会非常慢,并且某些过程方法的性能可能会更好.
Demo ---> http://www.sqlfiddle.com/#!12/bdf7e/9
however for huge table the performance of this query could be horribly slow, and some procedural approach might perform better.
这篇关于合并重叠的时间间隔,如何?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!