MySQL两表连接：筛选时间戳相差5分钟内的数据

我有两张表poolTemp和weather，均包含timeStamp和tempC字段。需求是关联两表的tempC，仅保留poolTemp.timeStamp与weather.timeStamp相差5分钟内的数据。其中poolTemp.timeStamp每5分钟记录一次，weather.timeStamp每小时记录一次。

我编写了如下SQL语句，但未得到预期结果：

SELECT DISTINCT poolTemp.time, poolTemp.tempC
FROM poolTemp
LEFT JOIN weather
ON TIMESTAMPDIFF(MINUTE,weather.time, poolTemp.time) < 15

预期仅显示时间戳相差15分钟内的数据，但实际返回了poolTemp的所有时间戳记录（每5分钟一条的温度数据）。

示例数据 #

poolTemp表 #

time;tempC
2024-01-22 12:25:05;24.63 <-- 应该被选中
2024-01-22 12:20:05;24.58 <-- 应该被选中
2024-01-22 12:15:09;24.72
2024-01-22 12:10:05;24.58
2024-01-22 12:05:05;24.58
2024-01-22 12:00:09;24.72
2024-01-22 11:55:05;24.92
2024-01-22 11:50:04;24.59
2024-01-22 11:45:09;24.42
2024-01-22 11:40:05;24.48 <-- 应该被选中
2024-01-22 11:35:05;24.58 <-- 应该被选中
2024-01-22 11:30:09;24.58 <-- 应该被选中
2024-01-22 11:25:05;24.64 <-- 应该被选中
2024-01-22 11:20:05;24.74 <-- 应该被选中
2024-01-22 11:15:09;24.74
2024-01-22 11:10:05;24.53
2024-01-22 11:05:05;24.53 
2024-01-22 11:00:09;24.64
2024-01-22 10:55:05;24.84
2024-01-22 10:50:05;25.24
2024-01-22 10:45:09;25.47 <-- 应该被选中
2024-01-22 10:40:05;25.47 <-- 应该被选中
2024-01-22 10:35:05;25.47 <-- 应该被选中
2024-01-22 10:30:09;25.36 <-- 应该被选中
2024-01-22 10:25:05;25.36 <-- 应该被选中
2024-01-22 10:20:05;25.26 <-- 应该被选中

weather表 #

time;tempC;
2024-01-22 12:34:02;10.50
2024-01-22 11:34:02;9.90
2024-01-22 10:34:02;9.09
2024-01-22 09:34:02;8.49
2024-01-22 08:34:02;8.26

问题原因 #

1. LEFT JOIN特性：LEFT JOIN会强制返回左表（poolTemp）的所有记录，哪怕右表（weather）没有匹配的行，这就是为什么你得到了所有池温记录。
2. 时间差判断不完整：原条件只检查了poolTemp时间比weather时间晚不到15分钟的情况，没处理poolTemp时间更早的情况，而且没有限制差值的绝对值。

解决方案 #

改用INNER JOIN并修正时间差判断逻辑，确保只保留时间差绝对值在15分钟内的匹配行（如果需求是5分钟，把15改成5即可）：

SELECT DISTINCT poolTemp.time, poolTemp.tempC, weather.tempC AS weather_tempC
FROM poolTemp
INNER JOIN weather
ON ABS(TIMESTAMPDIFF(MINUTE, weather.time, poolTemp.time)) <= 15;

关键说明 #

• INNER JOIN：只返回两表满足匹配条件的行，过滤掉左表中没有对应天气记录的行。
• ABS()：取时间差的绝对值，覆盖poolTemp时间在weather时间之前或之后的两种情况。
• <=15：明确时间差不超过15分钟，和你示例中标记的"应该被选中"的记录逻辑一致。

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