mysql - 在数据库中查找在一段时间内重复的记录

Question

我在 MySQL 中有一系列记录，代表设备的故障排除事件。我感兴趣的是查找哪些设备在给定时间跨度内有连续的故障排除事件。

例如，我想知道 5 天内发生了两个或更多故障排除事件，但我不确定如何构建查询以列出所有设备。

我有两个类似的表:

devices
-----
id    name

events
-----
id    device_id    datetime

使用此示例数据:

events
-----
id    device_id    datetime
---   ---          ---
1     1            2011-01-01
2     1            2011-06-15
3     2            2011-06-18
4     1            2011-06-18
5     2            2011-06-20

我想构建一个提供如下结果的查询:

device_id    event_id    datetime
1            2           2011-06-15
1            4           2011-06-18
2            3           2011-06-18
2            5           2011-06-20

由于 2011-01-01 的事件被隔离超过 5 天，因此应将其省略。

我将不胜感激有关如何处理此问题的指示。

Answer 1

SELECT devices.`device_id`, events.`id` AS event_id, events.`datetime`
FROM devices
INNER JOIN events ON events.`id` = devices.`id`
WHERE events.`datetime` BETWEEN DATE_SUB(CURDATE(), INTERVAL 5 DAY) AND CURDATE()
ORDER BY devices.`device_id` ASC

这将查找过去 5 天内发生事件的所有设备。您可以将 CURDATE() 替换为任何开始日期。