我一直在尝试回答这个问题:如果行具有重叠的时间间隔,则对行进行分组 https://stackoverflow.com/questions/59656420/group-rows-if-they-have-overlapping-time-intervals/59666764?noredirect=1#comment105496122_59666764
我想出了一个对我来说效果很好的解决方案,但我无法在 fidel 上复制。现在我想知道这是为什么。
data:
create table your_table (id int(11), start_time time, end_time time);
insert into your_table (id, start_time, end_time) values (102, '11:01:00', '11:30:00');
insert into your_table (id, start_time, end_time) values (101, '10:00:00', '10:20:00');
insert into your_table (id, start_time, end_time) values (100, '10:00:00', '12:00:00');
insert into your_table (id, start_time, end_time) values (100, '10:15:00', '12:30:00');
insert into your_table (id, start_time, end_time) values (100, '12:15:00', '12:45:00');
insert into your_table (id, start_time, end_time) values (100, '13:00:00', '14:00:00');
insert into your_table (id, start_time, end_time) values (101, '09:00:00', '13:00:00');
insert into your_table (id, start_time, end_time) values (101, '09:30:00', '13:30:00');
insert into your_table (id, start_time, end_time) values (105, '10:30:01', '11:00:00');
insert into your_table (id, start_time, end_time) values (105, '10:00:00', '10:20:00');
insert into your_table (id, start_time, end_time) values (105, '10:21:00', '10:30:00');
insert into your_table (id, start_time, end_time) values (105, '14:30:01', '15:00:00');
insert into your_table (id, start_time, end_time) values (106, '10:00:00', '10:22:00');
insert into your_table (id, start_time, end_time) values (107, '10:19:00', '10:20:00');
insert into your_table (id, start_time, end_time) values (108, '10:01:00', '10:16:00');
基本上问题是如何创建列group_id
如下图所示。的想法group_id
是查看所有具有相同值的子组id
每当两行之间没有重叠时间时就增加 1 - 即当 start_time 为current row
> 比end_time
最后一行的
我的查询给出了上述输出,并且似乎在我的机器(MySQL 工作台)上运行良好:
select
id, start_time, end_time,
case when @id = id and start_time >= @end_time then @reminder + 1 else 1 end as group_id,
@id:=id as id_set,
@reminder:= case when @id = id and start_time >= @end_time then @reminder + 1 else 1 end as reminder,
@end_time:=end_time
from your_table t,
(select @id_check = 1) a,
(select @reminder = 1) b,
(select @end_time = '00:00:00') c
order by id, start_time;
但是在 fidel 上我无法复制它(所有值都是 1):
这是因为我的工作台版本吗?