如何修正SQL语句以正确获取车辆最后及上次服务日期？

问题原因 #

1. 跨车辆取数错误：原SQL中LAG函数未按Car_id分区，导致取到其他车辆的服务日期。
2. NULL未替换：未对无服务记录、单次服务的场景做NULL替换处理，不符合显示要求。

修正后的SQL #

WITH ServiceAgg AS (
    SELECT 
        Car_id,
        MAX(Service_Date) AS Last_Service_Date,
        -- 获取第二新的服务日期作为上次服务日期
        MAX(CASE WHEN rn = 2 THEN Service_Date END) AS Previous_Service_Date,
        COUNT(*) AS Service_Count
    FROM (
        SELECT 
            Car_id,
            Service_Date,
            -- 按车辆分组，服务日期降序排名，1为最新服务
            ROW_NUMBER() OVER (PARTITION BY Car_id ORDER BY Service_Date DESC) AS rn
        FROM Service
    ) t
    GROUP BY Car_id
)
SELECT 
    c.Car_id,
    -- 无服务记录时显示'-'，否则显示最后服务日期
    COALESCE(CAST(a.Last_Service_Date AS VARCHAR(10)), '-') AS 最后服务日期,
    -- 单次服务或无服务时显示'-'，否则显示上次服务日期
    CASE 
        WHEN a.Service_Count <= 1 OR a.Previous_Service_Date IS NULL THEN '-'
        ELSE CAST(a.Previous_Service_Date AS VARCHAR(10))
    END AS 上次服务日期
FROM Car c
LEFT JOIN ServiceAgg a ON c.Car_id = a.Car_id
ORDER BY c.Car_id;

关键改动说明 #

• 内层子查询通过PARTITION BY Car_id按车辆分组，ROW_NUMBER()给每辆车的服务记录按日期降序排名，确保只获取当前车辆的服务数据。
• 使用MAX(CASE WHEN rn=2 THEN Service_Date END)提取第二新的服务日期，作为上次服务日期。
• 通过COALESCE和CASE语句处理NULL场景：无服务记录时两个字段均显示'-'，单次服务时上次服务日期显示'-'。
• 用LEFT JOIN关联Car表，确保所有车辆都被查询到。

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