SQL查询：如何获取设备首次故障前的最新温度数据（含未故障设备）

方案合理性评估 #

你想到的用ISNULL替换极大值的方案完全合理。
原有查询无法返回无故障设备的核心原因是：未发生过故障的设备，查询首次FAIL时间的子查询返回NULL，而时间 < NULL的判断结果为UNKNOWN，会导致该设备的所有历史记录都被过滤，CROSS APPLY匹配不到结果自然不会返回该设备。
用ISNULL把NULL替换为9999-12-31这类极大时间后，无故障设备的所有历史记录时间都满足小于该值的条件，就能正确取到最新温度，完全符合需求。

更优的实现方式 #

可以用CTE先预计算所有设备的首次故障时间，避免每行设备都执行一次关联子查询，数据量较大时性能提升更明显，同时可读性也更好：

WITH DeviceFirstFail AS (
    SELECT 
        DeviceId,
        MIN(TimeStamp) AS FirstFailTime
    FROM DeviceStatusHistory
    WHERE State LIKE '0(%' -- 若State是单独存储的数值字段，直接写State=0即可
    GROUP BY DeviceId
)
SELECT 
    D.DeviceId,
    H.Temperature,
    H.TimeStamp
FROM Device D
CROSS APPLY (
    SELECT TOP 1 * 
    FROM History H
    WHERE H.DeviceId = D.DeviceId
        AND H.TimeStamp < ISNULL(
            (SELECT FirstFailTime FROM DeviceFirstFail F WHERE F.DeviceId = D.DeviceId),
            '9999-12-31 23:59:59.997'
        )
    ORDER BY H.TimeStamp DESC
) H
ORDER BY D.DeviceId

如果你的数据库支持窗口函数，还可以用以下写法：

WITH DeviceFirstFail AS (
    SELECT DeviceId, MIN(TimeStamp) FirstFailTime 
    FROM DeviceStatusHistory 
    WHERE State LIKE '0(%' 
    GROUP BY DeviceId
),
FilteredHistory AS (
    SELECT 
        H.*,
        ROW_NUMBER() OVER (PARTITION BY H.DeviceId ORDER BY H.TimeStamp DESC) rn
    FROM History H
    LEFT JOIN DeviceFirstFail F ON H.DeviceId = F.DeviceId
    WHERE H.TimeStamp < ISNULL(F.FirstFailTime, '9999-12-31 23:59:59.997')
)
SELECT DeviceId, Temperature, TimeStamp 
FROM FilteredHistory 
WHERE rn = 1
ORDER BY DeviceId

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