我有一个日期表。
我想查找和分组日期,相差36秒。
上一个日期和日期之间相差36秒
下次约会
如果是这样,它将是一个组,直到链的末端。
如果36秒的链断了,它将启动一个新组。
如果36秒钟内没有链,则使用finisDate和dateRangeIDs
返回null
我可以使用软件来完成此操作,但这很耗时。我需要通过MySQL做。
我的日期表(日期>%d。%m。%Y%H:%i:%s);
ID | Date
----+-----------------------
246 | 11.08.2014 07:54:38
247 | 11.08.2014 07:55:11
248 | 11.08.2014 07:55:45
249 | 11.08.2014 07:56:20
250 | 11.08.2014 07:56:53
251 | 11.08.2014 07:57:26
252 | 11.08.2014 07:58:01
253 | 11.08.2014 07:58:35
254 | 11.08.2014 07:59:10
255 | 11.08.2014 07:59:49
256 | 11.08.2014 07:59:58
257 | 11.08.2014 08:00:15
258 | 11.08.2014 08:00:23
259 | 11.08.2014 08:00:58
260 | 11.08.2014 08:01:01
261 | 11.08.2014 08:01:08
262 | 11.08.2014 09:12:11
预期结果:
mainID | startDate | finishDate | dateRangeIDs
--------+---------------------------+---------------------------+-----------------
246 | 11.08.2014 07:54:38 | 11.08.2014 07:59:10 | 247,248,249,250,251,252,253,254
255 | 11.08.2014 07:59:49 | 11.08.2014 08:01:08 | 256,257,258,259,260,261
262 | 11.08.2014 09:12:11 | NULL | NULL
最佳答案
最简单的方法使用lag()
获取上一个日期值。大多数MySQL版本均不提供该功能。一个相关的子查询确实使工作变得很痛苦。最后一个选项是变量:
select min(id) as mainid, min(date), max(date),
group_concat(id order by id) as ids
from (select t.*,
(@grp := if(@prev_date > date - interval 36 second,
if(@prev_date := date, @grp, @grp), -- keep the group the same
if(@prev_date := date, @grp + 1, @grp + 1)
)
) as grp
from (select t.*
from t
order by date
) t cross join
(select @prev_date := '', @grp := 0) params
) t
group by grp;
编辑:
您可以通过执行以下操作删除主要ID:
select min(id) as mainid, min(date), max(date),
substr(group_concat(id order by id), length(min(id)) + 1) as ids