MySQL查询：找出近3小时未上报数据的coordinatorId

需求：设备每小时上报一次数据，需筛选出近3小时未上报数据的coordinatorId。

现有coordinator表数据如下：

原SQL的问题 #

你尝试的SQL逻辑存在错误：

SELECT DISTINCT coordinatorId 
FROM coordinator 
WHERE (coordinator.time < now() - INTERVAL 180 minute) NOT IN (coordinator.time > now() - INTERVAL 180 minute);

NOT IN的语法要求右侧是一个值集合，但这里两边都是布尔判断条件，逻辑完全不成立，导致错误包含了近3小时有上报记录的coordinatorId=1。

正确的SQL写法 #

写法1：排除近3小时有上报的ID

先查询出近3小时有上报记录的coordinatorId，再筛选不在这个范围内的ID：

SELECT DISTINCT coordinatorId
FROM coordinator
WHERE coordinatorId NOT IN (
    SELECT DISTINCT coordinatorId
    FROM coordinator
    WHERE `time` >= NOW() - INTERVAL 3 HOUR
);

写法2：分组取最新上报时间（推荐）

按coordinatorId分组，统计每个ID的最新上报时间，筛选最新时间早于3小时前的记录：

SELECT coordinatorId
FROM coordinator
GROUP BY coordinatorId
HAVING MAX(`time`) < NOW() - INTERVAL 3 HOUR;

这种写法效率更高，直接通过分组聚合获取每个设备的最新状态，无需子查询多次扫描表。

写法3：LEFT JOIN筛选无匹配的ID

通过左关联近3小时有上报的记录，筛选出没有匹配结果的coordinatorId：

SELECT DISTINCT c.coordinatorId
FROM coordinator c
LEFT JOIN (
    SELECT DISTINCT coordinatorId
    FROM coordinator
    WHERE `time` >= NOW() - INTERVAL 3 HOUR
) recent ON c.coordinatorId = recent.coordinatorId
WHERE recent.coordinatorId IS NULL;

以上三种写法均能正确筛选出coordinatorId=2，符合需求。

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