如何在SQL中查询每个sensor_id对应最接近当前日期的最新时间值

你之前的写法存在两个问题：

1. TOP 1是SQL Server、Access等数据库的语法，MySQL不支持该写法
2. 即便替换为LIMIT 1也只能返回全表排序后的第一条最新记录，无法按sensor_id分组返回每个传感器的最新数据

方案1：MySQL 8.0+ 版本（窗口函数实现，推荐） #

使用ROW_NUMBER()窗口函数给每个sensor_id的记录按时间倒序排序，过滤出排序序号为1的记录即可：

SELECT sensor_id, event_type, value, `time`
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY sensor_id ORDER BY `time` DESC) AS rn
    FROM events
    -- 如需限定时间范围可在此处加WHERE条件，例如 WHERE `time` < '2021-01-01 00:00:01'
) AS temp
WHERE rn = 1;

注意：time属于SQL保留关键字，作为字段名引用时需要用反引号包裹避免语法报错

方案2：MySQL 5.x 版本（兼容旧版本实现） #

先通过分组查询拿到每个sensor_id对应的最大时间，再关联原表匹配得到对应的event_type等字段：

SELECT e.sensor_id, e.event_type, e.value, e.`time`
FROM events e
INNER JOIN (
    SELECT sensor_id, MAX(`time`) AS max_time
    FROM events
    -- 如需限定时间范围可在此处加WHERE条件
    GROUP BY sensor_id
) AS temp ON e.sensor_id = temp.sensor_id AND e.`time` = temp.max_time;

因为表结构中time字段有唯一约束，所以不会出现同一个sensor_id匹配到多条记录的问题。

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