如何用ROW_NUMBER() OVER PARTITION BY编写SQL获取车辆健康聚合结果

你不需要用ROW_NUMBER()窗口函数，按车辆ID分组后通过聚合判断诊断记录特征即可实现需求，语句兼容MySQL、PostgreSQL、SQL Server等主流数据库：

SELECT 
    car_id,
    CASE
        -- 规则2优先级最高：只要存在非good且非空的诊断就标记为需要维修
        WHEN COUNT(CASE WHEN diagnosis <> 'good' AND diagnosis IS NOT NULL THEN 1 END) > 0 
        THEN 'needs_work'
        -- 规则3优先级次之：没有需要维修的记录但存在未诊断的空值就标记为未知
        WHEN COUNT(CASE WHEN diagnosis IS NULL THEN 1 END) > 0
        THEN 'unknown'
        -- 规则1：所有记录都是good标记为健康
        ELSE 'good'
    END AS outcome
FROM carhealthreport
GROUP BY car_id
ORDER BY car_id;

执行结果匹配测试数据预期： #

原方案问题说明 #

你原来用ROW_NUMBER()的思路达不到预期，是因为窗口函数按diagnosis排序时，不同数据库对NULL值的排序优先级不一致，且单条排序结果没法同时覆盖「是否存在坏件」「是否存在未诊断项」两个判断维度，用分组聚合的逻辑更直观，也不存在数据库兼容问题。

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