如何在MySQL中查询落在多个时间范围内的事件数据

我有两张表，一张可通过子查询从summarized_idle_times生成多个时间范围，另一张是存储事件的devices_data表。需要找出所有事件日期落在任意时间范围内的事件行，并将其与对应的时间范围关联起来。

示例数据 #

时间范围表（子查询生成结果） #

事件表（devices_data） #

预期结果：返回ID为1、2、5的事件，同时关联对应的时间范围。

之前尝试的SQL因子查询返回多行，导致between条件无法匹配，无法正常运行：

select * from devices_data 
where type in ('unload', 'load') 
and devices_data.date between 
(select start_idle_time as start from summarized_idle_times)
and 
(select DATE_ADD(start_idle_time, INTERVAL idle_duration second) as end from summarized_idle_times)
order by devices_data.date desc

解决方案 #

方案1：JOIN关联时间范围（推荐，同时返回事件与对应时间区间） #

将动态生成的时间范围表作为子查询，与事件表通过日期区间条件做JOIN：

SELECT 
  d.*,
  r.start,
  r.end
FROM devices_data d
JOIN (
  -- 动态生成时间范围表
  SELECT 
    start_idle_time AS start,
    DATE_ADD(start_idle_time, INTERVAL idle_duration SECOND) AS end
  FROM summarized_idle_times
) r ON d.date BETWEEN r.start AND r.end
WHERE d.type IN ('unload', 'load')
ORDER BY d.date DESC;

方案2：EXISTS子查询（仅需事件数据时使用） #

如果只需要获取符合条件的事件行，不需要返回时间范围，用EXISTS性能更优：

SELECT *
FROM devices_data d
WHERE d.type IN ('unload', 'load')
AND EXISTS (
  SELECT 1
  FROM summarized_idle_times s
  WHERE d.date BETWEEN s.start_idle_time AND DATE_ADD(s.start_idle_time, INTERVAL s.idle_duration SECOND)
)
ORDER BY d.date DESC;

说明 #

之前的错误在于between后面用了两个独立的子查询，这两个子查询都返回多行，数据库无法判断事件日期应该对应哪一组起止时间。JOIN可以直接将事件与对应的时间范围关联，EXISTS则只做存在性判断，按需选择即可。

内容的提问来源于stack exchange，提问作者Ben Weisblatt