如何修改同表内连接SQL查询以支持批量DeviceId查询

没问题，我帮你调整这条SQL来支持查询一组DeviceId的记录，核心是给行号生成逻辑加上设备分区，同时调整设备ID的过滤条件。以下是修改后的完整语句：

SELECT X.DeviceId, Y.RecordedTime, X.RecordedTime, (DATEDIFF(SECOND, Y.RecordedTime, X.RecordedTime)) SECONDS 
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY DeviceId ORDER BY RecordedTime DESC) SNO, 
        [GCId], [DeviceId], [IgnitionStatus], [TruckNo], [TripId], [RecordedTime], [UploadTime], [SyncTime], EventCode 
    FROM [KYTE].[dbo].[KT_savetest1] 
    WHERE 
        DeviceId IN ('861693034654582', '86169303883567') -- 替换成你的目标设备ID列表
        AND (RecordedTime > '2018-01-22 00:00:05' AND RecordedTime < '2018-01-22 00:23:55') 
        AND EventCode IN (1, 101)
) X 
INNER JOIN (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY DeviceId ORDER BY RecordedTime DESC) SNO, 
        [GCId], [DeviceId], [IgnitionStatus], [TruckNo], [TripId], [RecordedTime], [UploadTime], [SyncTime], EventCode 
    FROM [KYTE].[dbo].[KT_savetest1] 
    WHERE 
        DeviceId IN ('861693034654582', '86169303883567') -- 保持与上方一致的设备ID列表
        AND (RecordedTime > '2018-01-22 00:00:05' AND RecordedTime < '2018-01-22 00:23:59') 
        AND EventCode IN (1, 101)
) Y 
ON X.DeviceId = Y.DeviceId -- 新增设备ID关联，确保只匹配同一设备的记录
AND X.SNO = Y.SNO - 1 
WHERE (DATEDIFF(SECOND, Y.RecordedTime, X.RecordedTime)) > 60

关键修改点说明： #

• 分区行号生成：给ROW_NUMBER()添加PARTITION BY DeviceId，让每个设备的记录独立生成序号，避免不同设备的序号交叉，保证前后关联的是同一设备的连续记录。
• 批量设备过滤：把原语句中DeviceId = '单个ID'改成DeviceId IN ('ID1', 'ID2', ...)，支持传入任意数量的设备ID。
• 关联条件增强：在JOIN的ON条件里新增X.DeviceId = Y.DeviceId，彻底避免跨设备的错误关联，确保数据准确性。

执行结果示例（对应你提供的测试数据）： #

86169303883567 2018-01-21 23:40:52 2018-01-21 23:41:53 61 
86169303883567 2018-01-21 23:27:55 2018-01-21 23:28:56 61 
86169303883567 2018-01-21 23:02:01 2018-01-21 23:03:02 61 
86169303883567 2018-01-21 22:49:04 2018-01-21 22:50:05 61

另外提个小建议：原SQL中X子查询的时间范围是< '2018-01-22 00:23:55'，而Y是< '2018-01-22 00:23:59'，如果这是笔误，建议统一成相同的时间范围，避免数据筛选不一致。

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