mysql - 在 WHERE 子句中合并具有相邻时间戳的行

Question

我有一个表availablities，其中包含用户的可用性:

row_id | user_id  | available_from      | available_to
-------------------------------------------------------------
1      | 1        | 2012-02-01 08:00:00 | 2012-02-01 09:00:00
2      | 1        | 2012-02-01 09:00:00 | 2012-02-01 10:00:00
3      | 2        | 2012-02-01 08:00:00 | 2012-02-01 10:00:00
4      | 3        | 2012-02-01 07:00:00 | 2012-02-01 12:00:00

我需要从这个表中获取特定事件的所有可用用户。

场景:获取开始于 2012-02-01 08:00:00 并结束于 2012-02-01 10:00:00 的事件的所有可用用户.

我在获取 user_ids (2,3) 时没问题:

SELECT `user_id` FROM `availablities`
WHERE `available_from` <= "2012-02-01 08:00:00"
AND `available_to` >= "2012-02-01 10:00:00"

虽然我很难找到一个也将返回 user_id (1) 的查询。为此，查询必须以某种方式组合 user_id (1) 的两行 (1) 和 (2) 因为只有这两行的总和可用性适合事件。

在一个完美的世界中，行 (1,2) 上的相邻可用性将只在一行中。它以这种方式保存是有原因的，我不能只是“优化”表中的数据以将相邻的可用性组合在一行中。

所以我需要的是在 mysql 中一种在给定时间范围的查询中返回 user_ids (1,2,3) 的方法 - 可能还是我必须找到另一种方法？

Answer 1

如果您需要在一个查询中执行此操作，您可以尝试结合使用 MySQL 用户定义变量和一些内联 View 技巧:

select  d.*
from
(
select u.user_id,u.consec as available_from,max(u.available_to) as available_to
from
(
select a.*,
case when @lastEndDate != a.available_from then @curStartDate := a.available_from else @curStartDate end as consec,
@lastEndDate := a.available_to as c
from availabilities a
inner join (select @curStartDate := null,@lastEndDate := "1970-01-01 00:00:00") as t
order by a.user_id,a.available_from
) u
group by u.user_id,u.consec
) d
where d.available_from <= "2012-02-01 08:00:00"
AND d.available_to >= "2012-02-01 10:00:00";

我对您的测试数据进行了一些测试，其中包括一个用户 (user_id = 4)，该用户在 2012-02-01 08:00:00 和 2012-02-01 08:30:00 之间可用，然后在 2012 年之间再次可用-02-01 09:00:00 和 2012-02-01 10:00:00。所以他在 2012-02-01 08:00:00 和 2012-02-01 10:00:00 之间的整个时间段内都不可用(茶歇？!)。

因此预期的结果是该时间段返回 user_id = 1，但没有返回 user_id =4。

这是我使用的测试:

drop table if exists availabilities;

create table availabilities
(row_id integer unsigned not null primary key,
user_id integer not null,
available_from datetime not null,
available_to datetime not null
);

insert into availabilities values (1,1,'2012-02-01 08:00:00','2012-02-01 09:00:00');
insert into availabilities values (2,1,'2012-02-01 09:00:00','2012-02-01 10:00:00');
insert into availabilities values (3,2,'2012-02-01 08:00:00','2012-02-01 10:00:00');
insert into availabilities values (4,3,'2012-02-01 07:00:00','2012-02-01 12:00:00');
insert into availabilities values (5,4,'2012-02-01 08:00:00','2012-02-01 08:30:00');
insert into availabilities values (6,4,'2012-02-01 09:00:00','2012-02-01 10:00:00');