MySQL多条件排序问题：tasks表按cost和status规则排序

需求梳理 #

• 优先级1：cost为数值且status为2701、2702的记录排在最前
• 优先级2：cost为null且status按2701→2702→2703排序的记录次之
• 优先级3：所有status为2703的记录（无论cost是否为null）排在最后

测试数据 #

cost status
1    2703
null 2702
2    2701

预期排序结果 #

cost status
2    2701
null 2702
1    2703 // 无论cost是否为null，status为2703的始终排在最后

原SQL问题分析 #

你尝试的SQL：

select * from tasks t1 where active = true 
ORDER BY t1.cost IS NULL, t1.cost asc, t1.statusId asc

未区分status=2703的特殊优先级，也没对cost非null时的status做筛选排序，无法满足需求。

正确SQL实现 #

SELECT * 
FROM tasks t1 
WHERE active = true
ORDER BY
    -- 第一维度：将status=2703的记录归到最后一组
    CASE WHEN t1.status = 2703 THEN 2 ELSE 1 END ASC,
    -- 第二维度：cost非null的记录排在null记录前面
    t1.cost IS NULL ASC,
    -- 第三维度：同组内按status升序排列
    t1.status ASC;

排序逻辑拆解 #

1. 第一层级排序：通过CASE表达式给status=2703的标记为2，其余标记为1，升序排列后直接满足“2703记录最后”的要求。
2. 第二层级排序：t1.cost IS NULL ASC中，cost非null时表达式返回0，null时返回1，升序后实现“非null记录优先”的分层。
3. 第三层级排序：t1.status ASC确保同一分组内（比如cost非null且status≠2703的组、cost为null的组），按status从小到大排序，匹配需求内的子规则。

验证结果 #

用提供的测试数据执行上述SQL，会得到与预期完全一致的排序结果。

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