如何用单SQL实现基于状态的ETA列混合排序（Laravel+Postgres）

需求说明：

• pending、ordered、sailing状态的订单，按eta列升序排列
• complete状态的订单，按eta列降序排列
• 所有complete订单必须排在列表底部
• 要求用单个SQL查询实现，避免多查询合并

现有orders表示例数据 #

期望显示结果 #

注：仅eta列参与排序，所有'complete'订单位于底部

解决方案（Postgres + Laravel） #

原生SQL实现 #

利用CASE表达式在ORDER BY中分层控制排序逻辑：

SELECT 订单号, status, eta
FROM orders
ORDER BY
    -- 优先将complete订单放在最后
    CASE WHEN status = 'complete' THEN 1 ELSE 0 END,
    -- 非complete订单按eta升序排列
    CASE WHEN status != 'complete' THEN TO_DATE(eta, 'DD-MM-YYYY') END ASC,
    -- complete订单按eta降序排列
    CASE WHEN status = 'complete' THEN TO_DATE(eta, 'DD-MM-YYYY') END DESC;

Laravel查询构建器实现 #

对应Laravel代码如下（适配Postgres环境）：

$orders = DB::table('orders')
    ->select('订单号', 'status', 'eta')
    ->orderByRaw("CASE WHEN status = 'complete' THEN 1 ELSE 0 END")
    ->orderByRaw("CASE WHEN status != 'complete' THEN TO_DATE(eta, 'DD-MM-YYYY') END ASC")
    ->orderByRaw("CASE WHEN status = 'complete' THEN TO_DATE(eta, 'DD-MM-YYYY') END DESC")
    ->get();

逻辑说明 #

1. 第一个排序条件通过CASE给complete订单标记为1、其他状态标记为0，确保complete订单始终排在底部；
2. 第二个排序条件仅对非complete订单生效，将字符串格式的eta转为日期类型后升序排序；
3. 第三个排序条件仅对complete订单生效，同样转换日期类型后降序排序；
4. 如果你的eta字段本身就是Postgres日期类型，可直接去掉TO_DATE(eta, 'DD-MM-YYYY')转换部分。

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