MySQL - 查找丢失的数据

Question

我正在尝试从一个表中列出每一次丢失的数据，该表每天应包含 24 行数据(每小时一行)。来自以下示例:

Datetime
2015-05-01 00:00:00
2015-05-01 01:00:00
2015-05-01 02:00:00
2015-05-01 03:00:00
2015-05-01 07:00:00
2015-05-01 08:00:00
2015-05-01 09:00:00
...

我想返回:

Datetime
2015-05-01 04:00:00
2015-05-01 05:00:00
2015-05-01 06:00:00

我可以像这样返回丢失数据的第一个小时和最后一个小时:

SELECT t1.datetime AS `from`, t2.datetime AS `to`
FROM test.table AS t1
JOIN test.table AS t2
  ON t1.datetime < t2.datetime
LEFT JOIN test.table AS t3
  ON t3.datetime > t1.datetime
   AND t3.datetime < t2.datetime
WHERE t3.datetime IS NULL
  AND t2.datetime > DATE_ADD(t1.datetime, INTERVAL 60 MINUTE)
ORDER BY t1.datetime;

但这只是 Google 所能找到的。

非常感谢任何帮助。

Answer 1

您需要创建一个包含所有时间的临时表，然后连接临时表和保存真实数据的表。

SELECT temp_table.datetime AS MissingDT
FROM temp_table
LEFT JOIN data_table ON temp_table.datetime = data_table.datetime
WHERE data_table.datetime IS NULL;

DATA_TABLE = Table that holds real data

TEMP_TABLE = Table that holds dummy data - i.e. all the hours