SQL优化查询：获取各UnitID下所有不重复VehicleID的最新记录

你的需求是每个UnitID下，每个不重复VehicleID的最新记录，但原SQL仅按UnitID取了全局最新的单条记录，未考虑VehicleID的维度，因此结果不符合预期。以下是优化后的查询方案：

优化后的SQL查询 #

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY UnitID, VehicleID ORDER BY timestamp DESC) AS rn
    FROM ChannelValue WITH (NOLOCK)
    WHERE timestamp > '2023-05-09 12:11:41.596'
) t
WHERE rn = 1
ORDER BY timestamp;

方案说明 #

1. 内层查询通过PARTITION BY UnitID, VehicleID将数据按「设备ID+车辆ID」的组合分组，每个组内按时间戳倒序排序
2. ROW_NUMBER()为每个分组内的记录分配序号，最新的记录序号为1
3. 外层筛选序号为1的记录，即可得到每个UnitID+VehicleID组合的最新记录，同时保留所有列

原查询的问题分析 #

原SQL的子查询仅按UnitID分组取最大timestamp，再匹配主表记录，这会导致：

• 一个UnitID下只有对应最大时间戳的记录被返回
• 无法覆盖同一个UnitID下，其他VehicleID各自的最新记录

性能优化建议 #

若表数据量较大，可创建复合索引提升查询效率：

CREATE INDEX IX_ChannelValue_UnitID_VehicleID_Timestamp ON ChannelValue(UnitID, VehicleID, timestamp DESC);

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