如何在MySQL中获得一系列重叠事件
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了如何在MySQL中获得一系列重叠事件，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6735字，纯文字阅读大概需要10分钟。
内容图文
我有一个重叠时间段的表.我想将连续的重叠时间事件分组(即不与时间间隔分开).
ID StartDate EndDate
1 2013-01-30 2013-01-31
2 2013-01-31 2013-01-31
3 2013-01-29 2013-01-31
4 2013-01-25 2013-01-28
5 2013-01-29 2013-01-30
6 2013-02-01 2013-02-01
7 2013-01-31 2013-02-02
8 2013-02-04 2013-02-05
9 2013-02-05 2013-02-06
10 2013-02-08 2013-02-09
01-24 01-25 01-26 01-27 01-28 01-29 01-30 01-31 02-01 02-02 02-03 02-04 02-05 02-06 02-07 02-08 02-09
1 --------------
2 -----
3 ---------------------
4 -----------------------------
5 ------------
6 -----
7 --------------------
8 -------------
9 -------------
10 --------------
因此,我希望有以下四个时间组：
第1组(身份证号码：1,2,3,5,6,7)
第2组(同上：4)
第3组(身份证号码：8,9)
第4组：(Id：10)
在Sql中有一个简单的方法吗？这是我的示例表的create sql：
DROP TABLE IF EXISTS tb_data_log;
CREATE TABLE tb_data_log (
`event_id` int(10) unsigned NOT NULL,
`startdate` date DEFAULT NULL,
`enddate` date DEFAULT NULL
);
INSERT INTO tb_data_log VALUES (1, '2013-01-30', '2013-01-31');
INSERT INTO tb_data_log VALUES (2, '2013-01-31', '2013-01-31');
INSERT INTO tb_data_log VALUES (3, '2013-01-29', '2013-01-31');
INSERT INTO tb_data_log VALUES (4, '2013-01-25', '2013-01-28');
INSERT INTO tb_data_log VALUES (5, '2013-01-29', '2013-01-30');
INSERT INTO tb_data_log VALUES (6, '2013-02-01', '2013-02-01');
INSERT INTO tb_data_log VALUES (7, '2013-01-31', '2013-02-02');
INSERT INTO tb_data_log VALUES (8, '2013-02-04', '2013-02-05');
INSERT INTO tb_data_log VALUES (9, '2013-02-05', '2013-02-06');
INSERT INTO tb_data_log VALUES (10, '2013-02-08', '2013-02-09');
编辑#1：
看起来问题有点难以理解,这是所需的输出：
GroupID StartDate EndDate Overlapped Id
1 2013-01-29 2013-02-02 1, 2, 3, 5, 6, 7
2 2013-01-25 2013-01-28 4
3 2013-02-04 2013-02-06 8,9
4 2013-02-08 2013-02-09 10
解决方法:
这是一个解决方案.它应该工作并且不使用存储过程：
select per_start,per_end,group_concat(contained.event_id) from tb_data_log contained,(
select distinct start.startdate as per_start,
finish.enddate as per_end
from tb_data_log start join tb_data_log finish
on start.startdate <= finish.enddate -- first find all possible periods
where not exists (-- make sure there are two events in the period which do not overlap and between whom there is no event.
select * from tb_data_log a, tb_data_log b where
a.enddate < b.startdate and
a.startdate>=start.startdate and
b.enddate<=finish.enddate and not exists
(
select * from tb_data_log inside where
inside.event_id <> a.event_id
and inside.event_id<> b.event_id
and inside.enddate > a.enddate and inside.startdate < b.startdate
)
)
and not exists (-- make sure there is no longer period
select * from tb_data_log later where later.startdate<=finish.enddate and later.enddate >finish.enddate
)
and not exists (-- make sure there is no longer period
select * from tb_data_log earlier where earlier.startdate<start.startdate and earlier.enddate >=start.startdate
)
) periods where contained.enddate<=per_end and contained.startdate>=per_start
group by per_start,per_end
我们的想法是首先通过自己加入表来找到所有可能的时期.然后对于每个周期P,确保没有一对周期A,B使得A在B之前(没有重叠),两者都包含在P中,并且它们之间没有事件.还要确保这不是最长的时间段.
这是我发布的先前解决方案,情况更糟.保持参考
它可能效率不高.我用过这里选择的答案：
How to get list of dates between two dates in mysql select query
请注意,此查询将在300年后停止工作！
select per_start,per_end,group_concat(contained.event_id) from tb_data_log contained,(
select distinct start.startdate as per_start,
finish.enddate as per_end
from tb_data_log start, tb_data_log finish
where start.startdate <= finish.enddate -- first find all possible periods
and not exists (-- make sure there are no two consecutive days that are not contained in some event period.
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) day1, adddate('1970-01- 01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0+1) day2 from
(select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where day1 between start.startdate and finish.enddate and day2 between start.startdate and finish.enddate
and not exists (
select * from tb_data_log where tb_data_log.startdate <= cast(day1 as date) and tb_data_log.enddate >= cast(day2 as date)
)
)
and not exists (-- make sure there is no longer period
select * from tb_data_log later where later.startdate<=finish.enddate and later.enddate >finish.enddate
)
and not exists (-- make sure there is no longer period
select * from tb_data_log earlier where earlier.startdate<start.startdate and earlier.enddate >=start.startdate
)
) periods where contained.enddate<=per_end and contained.startdate>=per_start
group by per_start,per_end
我们的想法是首先通过自己加入表来找到所有可能的时期.然后,对于每个时段,请确保该时段中不包含连续的天数,但表中的某些事件时段不包含这些连续天数.还要确保这不是最长的时间段.
我认为这个查询的性能可以有所改善.
内容总结
以上是互联网集市为您收集整理的如何在MySQL中获得一系列重叠事件全部内容，希望文章能够帮你解决如何在MySQL中获得一系列重叠事件所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】