求助：从已部署设备的GPS表中提取最新记录的SQL实现

核心思路 #

用窗口函数**RANK()或ROW_NUMBER()**给每个设备的GPS记录按时间倒序排名，取排名第一的最新记录，同时通过关联已授权设备表筛选范围。

具体代码实现 #

方案1：CTE + RANK() #

如果允许同一设备同一时间有多条记录都被保留（比如同一时间采集到多个GPS点位），用RANK()更合适：

WITH RankedGPS AS (
    SELECT 
        g.*,
        RANK() OVER (PARTITION BY g.DeviceId ORDER BY g.RecordedDate DESC) AS GPSRank
    FROM GPSLocationTable g
    -- 先关联已授权设备表，只处理已授权设备的GPS数据
    INNER JOIN CommissionedDeviceTable c 
        ON g.DeviceId = c.DeviceId
)
-- 取每个设备排名第一的最新记录
SELECT DeviceId, Latitude, Longitude, RecordedDate -- 替换成你实际需要的字段
FROM RankedGPS
WHERE GPSRank = 1;

方案2：CTE + ROW_NUMBER() #

如果同一设备同一时间仅需保留一条记录（即使有重复时间的记录也只取一条），用ROW_NUMBER()更精准：

WITH RankedGPS AS (
    SELECT 
        g.*,
        ROW_NUMBER() OVER (PARTITION BY g.DeviceId ORDER BY g.RecordedDate DESC) AS GPSRowNum
    FROM GPSLocationTable g
    INNER JOIN CommissionedDeviceTable c 
        ON g.DeviceId = c.DeviceId
)
SELECT DeviceId, Latitude, Longitude, RecordedDate
FROM RankedGPS
WHERE GPSRowNum = 1;

额外扩展：关联DeviceTable获取更多设备信息 #

如果需要从DeviceTable中获取设备名称、类型等额外字段，只需在最终查询时关联即可：

WITH RankedGPS AS (
    SELECT 
        g.*,
        ROW_NUMBER() OVER (PARTITION BY g.DeviceId ORDER BY g.RecordedDate DESC) AS GPSRowNum
    FROM GPSLocationTable g
    INNER JOIN CommissionedDeviceTable c 
        ON g.DeviceId = c.DeviceId
)
SELECT 
    d.DeviceName, 
    d.DeviceType, 
    r.Latitude, 
    r.Longitude, 
    r.RecordedDate
FROM RankedGPS r
INNER JOIN DeviceTable d 
    ON r.DeviceId = d.DeviceId
WHERE r.GPSRowNum = 1;

关键细节说明 #

• PARTITION BY g.DeviceId：按设备ID分组，保证每个设备的GPS记录单独排序
• ORDER BY g.RecordedDate DESC：按记录时间倒序排列，最新的记录排名第一
• 提前关联CommissionedDeviceTable：先过滤掉未授权设备的数据，减少后续计算量，提升查询效率

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