求助：SQLite获取各Serial传感器最新数据的查询语句

需求说明 #

使用SQLite与SQLiteStudio处理传感器采集数据，数据字段包括Serial、Key、Time_Stamp、value，需提取每个Serial对应的最新一条记录。

输入数据 #

预期结果 #

尝试的错误语句 #

SELECT sensor_data.[sensor_serial], sensor_data.[key],sensor_data.[time], sensor_data.[value]
FROM sensor_data
WHERE sensor_serial = (SELECT  sensor_serial FROM sensor_data)
ORDER BY time DESC

错误输出 #

0   12  20230531010945  1
0   11  20230531010912  0
0   10  20230531010657  1
0   9   20230531010648  0
0   5   20230531010346  0
0   4   20230531000935  1
0   3   20230531000928  0
0   1   20230531000652  1

问题分析 #

原语句逻辑错误：WHERE sensor_serial = (SELECT sensor_serial FROM sensor_data) 中子查询返回多个值时，= 仅匹配第一个返回的Serial（此处为0），因此只会输出Serial=0的所有记录并按时间倒序，无法实现“每个Serial取最新记录”的需求。

正确SQL查询语句 #

方法一：子查询关联（兼容所有SQLite版本） #

SELECT sd.Serial, sd.Key, sd.Time_Stamp, sd.value
FROM sensor_data sd
INNER JOIN (
    SELECT Serial, MAX(Time_Stamp) AS latest_time
    FROM sensor_data
    GROUP BY Serial
) latest ON sd.Serial = latest.Serial AND sd.Time_Stamp = latest.latest_time;

方法二：窗口函数（SQLite 3.25及以上版本支持） #

SELECT Serial, Key, Time_Stamp, value
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Serial ORDER BY Time_Stamp DESC) AS rn
    FROM sensor_data
) t
WHERE rn = 1;

语句说明 #

• 方法一：先通过分组查询得到每个Serial对应的最新时间戳，再与原表关联，筛选出对应记录。
• 方法二：利用ROW_NUMBER()窗口函数按Serial分组，每组内按Time_Stamp倒序排序，取每组第一条（rn=1）即为该Serial的最新记录。

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