如何在ORDER BY中使用CASE WHEN？解决SQL查询中process_type不在选择列表的错误

首先来说说你遇到的问题：你写的查询报错是因为SQL分组查询的规则——当使用GROUP BY时，SELECT列表里的列要么是GROUP BY里的分组字段，要么是用聚合函数处理过的字段。你的process_type既不在分组字段里，也没加聚合函数，所以数据库不知道该返回分组里哪一条的process_type。另外，你ORDER BY里直接用未聚合的create_date/modify_date也是不符合分组查询规则的。

针对你的需求，用窗口函数ROW_NUMBER()来实现会更清晰准确，下面是调整后的查询语句：

WITH ranked_vehicles AS (
    SELECT 
        pk_id, customer_id, vehicle_id, relation_type, end_date, is_owner, create_date, modify_date, process_type,
        -- 按分组维度分区，然后根据规则排序，给每条记录打序号
        ROW_NUMBER() OVER (
            PARTITION BY customer_id, vehicle_id, relation_type, is_owner
            ORDER BY 
                CASE 
                    WHEN MAX(create_date) OVER (PARTITION BY customer_id, vehicle_id, relation_type, is_owner) 
                         >= MAX(modify_date) OVER (PARTITION BY customer_id, vehicle_id, relation_type, is_owner)
                    THEN create_date 
                    ELSE modify_date 
                END DESC
        ) AS rn
    FROM #Result veh (NOLOCK)
    WHERE veh.end_date IS NULL AND veh.is_owner = 0 AND veh.relation_type = 1
)
SELECT pk_id, customer_id, vehicle_id, relation_type, end_date, is_owner, create_date, modify_date, process_type
FROM ranked_vehicles
WHERE rn = 1;

逻辑说明： #

1. 用WITH子句创建临时结果集ranked_vehicles：
   • PARTITION BY指定分组维度，和你原来的GROUP BY逻辑一致；
   • 用MAX(...) OVER (PARTITION BY ...)计算每组的最大create_date和最大modify_date，以此判断排序规则；
   • 按照判断后的规则给组内的记录降序排序并打序号rn，序号为1的就是我们要的目标记录。
2. 最后筛选出rn=1的记录，既能得到目标process_type，也能获取整条记录的所有字段。

针对你提供的样本数据，每组的MAX(create_date)是2021-09-19，MAX(modify_date)是2021-09-13，所以按create_date降序排序后，序号1的是pk_id=4的记录，对应的process_type=3，完全符合你的期望结果。

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