如何在SQL排序时忽略unitloadsequencenumber的NULL值，改用line_number排序？

需求说明 #

• 排序规则：优先按unitloadsequencenumber（装车顺序号）排序，该字段为NULL时，改用line_number（行号）排序
• 业务场景：trip_id将所有订单分组，一个order_number对应多条记录，装车顺序号决定货物上车的先后。比如要先展示装车顺序号为1的记录，接着展示同订单ASDF-2里行号2.0、3.0的记录

示例数据 #

trip_id  order_number  line_number  model unitloadsequencenumber
1        ASDF-2             1.0            C     1
1        ASDF-2             2.0            A     NULL
1        ASDF-2             3.0            B     NULL
1        ASDF-2             4.0            A     2
1        ASDF-2             5.0            C     NULL
1        ASDF-1             1.0            C     3
1        ASDF-1             2.0            B     NULL

尝试的SQL（未达预期） #

SELECT *
FROM public.vendor_trips_detail
ORDER BY
COALESCE(unitloadsequencenumber, CAST(line_number AS FLOAT));

问题原因&解决方案 #

你这SQL的问题在于：只把两个字段合并成一个值排序，但NULL值的记录需要归到对应订单下，紧跟在同订单有装车顺序号的记录后面。原写法会把行号2.0和装车顺序号2混排，导致顺序乱掉。

正确的排序要分三层逻辑：

1. 先按trip_id分组，保证同一趟车的记录放一起
2. 再按order_number分组，同一订单的记录不拆分
3. 最后优先按装车顺序号排序，空值记录则按行号排序，且要排在同订单所有有装车顺序号的记录之后

方案一：子查询获取订单最大装车号 #

SELECT *
FROM public.vendor_trips_detail
ORDER BY
    trip_id,
    order_number,
    -- 空值用订单最大装车号+行号，确保排在同订单有值记录后面，同时按行号排序
    COALESCE(unitloadsequencenumber, (SELECT MAX(unitloadsequencenumber) FROM public.vendor_trips_detail t WHERE t.order_number = vendor_trips_detail.order_number) + line_number);

方案二：窗口函数（更高效） #

SELECT *
FROM (
    SELECT 
        *,
        -- 提前计算每个订单的最大装车顺序号
        MAX(unitloadsequencenumber) OVER (PARTITION BY order_number) AS max_order_seq
    FROM public.vendor_trips_detail
) t
ORDER BY
    trip_id,
    order_number,
    COALESCE(unitloadsequencenumber, max_order_seq + line_number);

预期结果 #

执行后会得到符合业务要求的排序：

trip_id  order_number  line_number  model unitloadsequencenumber
1        ASDF-2             1.0            C     1
1        ASDF-2             2.0            A     NULL
1        ASDF-2             3.0            B     NULL
1        ASDF-2             4.0            A     2
1        ASDF-2             5.0            C     NULL
1        ASDF-1             1.0            C     3
1        ASDF-1             2.0            B     NULL

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