如何在SQL Server中基于最新日期判定车辆当前运行状态

核心逻辑：先过滤仅保留Start、Failed两种有效状态，取每辆车最新的状态记录做判定。

方案1：窗口函数实现（推荐，性能更优） #

假设你的表名为vehicle_status，可根据实际表名修改：

WITH ranked_status AS (
    SELECT
        Vehicle_ID,
        status,
        ROW_NUMBER() OVER(PARTITION BY Vehicle_ID ORDER BY date_on DESC) AS rn
    FROM vehicle_status
    WHERE status IN ('Start', 'Failed')
)
SELECT
    Vehicle_ID AS VehicleID,
    CASE status WHEN 'Start' THEN 'Yes' ELSE 'No' END AS [Running?]
FROM ranked_status
WHERE rn = 1

方案2：聚合子查询实现 #

SELECT
    v.Vehicle_ID AS VehicleID,
    CASE v.status WHEN 'Start' THEN 'Yes' ELSE 'No' END AS [Running?]
FROM vehicle_status v
INNER JOIN (
    SELECT Vehicle_ID, MAX(date_on) AS latest_date
    FROM vehicle_status
    WHERE status IN ('Start', 'Failed')
    GROUP BY Vehicle_ID
) t ON v.Vehicle_ID = t.Vehicle_ID AND v.date_on = t.latest_date
WHERE v.status IN ('Start', 'Failed')

注意事项 #

• 若同一车辆同一最大日期同时存在Start和Failed两条记录，可在窗口函数的ORDER BY后追加优先级规则，比如ORDER BY date_on DESC, CASE status WHEN 'Failed' THEN 1 ELSE 2 END即可在冲突时优先判定为未运行。
• SQL Server中带特殊符号的列名需要用方括号[]包裹转义。

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