SQL排序需求：如何按指定门店顺序优先展示旧订单？

现有表结构 #

stores表 #

+----------+------------+
|       id |       name |
+----------+------------+
|        1 |   storeone | 
|        2 |   storetwo | 
|        3 |  storethree| 
+----------+------------+

orders表 #

+----------+------------+------------+
|       id |    storeid | date       | 
+----------+------------+------------+
|        1 |          1 | 2024-07-01 | 
|        2 |          2 | 2024-06-01 | 
|        3 |          3 | 2024-07-22 | 
|        4 |          2 | 2024-06-11 | 
|        5 |          3 | 2024-06-14 | 
+----------+------------+------------+

当前执行的SQL及结果 #

查询语句 #

SELECT `o`.*,`s`.`name` FROM `orders` as o LEFT JOIN `stores` as s
    ON s.id = o.storeid ORDER BY o.date,s.id;

查询结果 #

+----------+------------+------------+-------------
|       id |    storeid | date       | name
+----------+------------+------------+-------------
|        2 |          2 | 2024-06-01 | storetwo   |
|        4 |          2 | 2024-06-11 | storetwo   |
|        5 |          3 | 2024-06-14 | storethree |
|        1 |          1 | 2024-07-01 | storeone   |
|        3 |          3 | 2024-07-22 | storethree |
+----------+------------+------------+-------------

期望的排序结果 #

+----------+------------+------------+-------------
|       id |    storeid | date       | name
+----------+------------+------------+-------------
|        2 |          2 | 2024-06-01 | storetwo   |
|        4 |          2 | 2024-06-11 | storetwo   |
|        5 |          3 | 2024-06-14 | storethree |
|        1 |          1 | 2024-07-22 | storethree |
|        3 |          3 | 2024-07-01 | storeone |
+----------+------------+------------+-------------

解决方案 #

要实现优先展示指定门店的旧订单，再按其他门店顺序展示的需求，可通过CASE WHEN为指定门店设置优先级，结合门店最早订单日期、订单自身日期来调整排序逻辑：

调整后的SQL（单指定门店） #

SELECT `o`.*, `s`.`name` 
FROM `orders` as o 
LEFT JOIN `stores` as s ON s.id = o.storeid 
ORDER BY 
    -- 将指定门店（此处为storeid=2）设为最高优先级
    CASE WHEN o.storeid = 2 THEN 1 ELSE 2 END,
    -- 其他门店按自身最早订单日期排序，整体订单更早的门店优先
    (SELECT MIN(date) FROM orders WHERE storeid = o.storeid),
    -- 门店内按订单日期升序，旧订单在前
    o.date,
    s.id;

调整后的SQL（多指定门店优先级） #

如果需要指定多个门店的展示顺序（比如先storetwo，再storethree，最后storeone），可修改CASE语句：

SELECT `o`.*, `s`.`name` 
FROM `orders` as o 
LEFT JOIN `stores` as s ON s.id = o.storeid 
ORDER BY 
    CASE o.storeid
        WHEN 2 THEN 1
        WHEN 3 THEN 2
        ELSE 3
    END,
    o.date,
    s.id;

逻辑说明 #

• 用CASE WHEN给目标门店分配最小排序值，确保其排在最前列
• 非指定门店通过子查询获取自身最早订单日期，保证整体订单更早的门店优先展示
• 门店内部按订单日期升序排列，实现旧订单在前的效果

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