MySQL自定义状态排序规则：按指定状态顺序及日期排序结果

要满足你指定的排序规则，我们可以通过在ORDER BY子句中结合CASE语句来实现，既控制state的优先级顺序，又针对不同状态设置date的排序方向。

通用SQL方案（适用于大多数数据库） #

SELECT *
FROM mytable
ORDER BY
    -- 第一步：按指定的state顺序排序（Open → Hold → Closed）
    CASE state
        WHEN 'Open' THEN 1
        WHEN 'Hold' THEN 2
        WHEN 'Closed' THEN 3
    END,
    -- 第二步：Closed状态按date降序排列
    CASE WHEN state = 'Closed' THEN date END DESC,
    -- 第三步：Open和Hold状态按date升序排列
    CASE WHEN state IN ('Open', 'Hold') THEN date END ASC;

针对MySQL的简化方案 #

如果你使用的是MySQL，可以利用FIELD()函数简化state的排序逻辑，代码更简洁：

SELECT *
FROM mytable
ORDER BY
    FIELD(state, 'Open', 'Hold', 'Closed'),
    -- Closed状态降序，其他状态升序
    CASE WHEN state = 'Closed' THEN date END DESC,
    date ASC;

逻辑解释 #

1. state优先级控制：通过第一个CASE（或MySQL的FIELD()）给每个state分配一个权重值，确保Open（权重1）排在最前，Hold（权重2）次之，Closed（权重3）最后，完全匹配你要求的顺序。
2. date排序方向区分：
   • 对于Closed状态，我们单独用CASE提取date并按降序排序；
   • 对于Open和Hold状态，直接按date升序排序即可。
     因为前面已经按state分组，这两个排序规则只会作用在各自对应的状态组内，不会互相干扰。

测试结果 #

用你提供的数据执行上述SQL后，得到的排序结果如下：

完全符合你要求的排序规则：先按Open→Hold→Closed的顺序分组，Open和Hold组内按date升序，Closed组内按date降序。

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