MySQL如何编写带ORDER BY的子查询解决多字段排序异常

问题说明 #

现有工单查询SQL原排序规则为：优先按工单状态升序排列，同状态下按工单发布日期降序展示最新数据，限制返回100条。需新增同维度下按紧急程度升序排序的规则，直接将urgent_id asc加入ORDER BY子句时，因字段优先级配置错误，导致2021年等老旧高紧急度工单被前置展示。
原有可正常运行的SQL如下：

SELECT t.`id_ticket`,ts.`status_desc`, t.`data`, t.`client_name`, t.`email`, t.`number`, nu.`emergency_desc`, tp.`problem_desc`, t.`details`, t.`staff`,t.updated,u.name as selected,t.status_id,t.emergency_id,t.creator_staff
FROM ticket t
LEFT JOIN  user u on u.id=t.dtg
LEFT JOIN problem_type tp ON t.problem_type = tp.problem_id
LEFT JOIN urgent_level nu on t.urgent_id =nu.urgent_id
LEFT JOIN ticket_status ts on t.status_id=ts.status_id
Where vis=1 ORDER BY t.status_id asc,t.data desc LIMIT 100

异常原因 #

MySQL的ORDER BY子句按照字段书写顺序从左到右分配排序权重，左侧字段的排序优先级永远高于右侧字段。如果将t.urgent_id asc放在t.data desc之前，排序逻辑会变成「同状态下先按紧急程度排序，再按日期排序」，直接打乱原有的日期优先规则，自然会出现老旧高紧急度工单排在前列的问题。

正确实现 #

写法1：直接调整ORDER BY字段顺序（性能最优） #

严格按照业务优先级从高到低排列排序字段：

• 第一优先级：工单状态升序（保留原有规则）
• 第二优先级：工单发布日期降序（保留原有同状态下最新工单在前的规则）
• 第三优先级：紧急程度升序（新增规则：仅当状态、发布日期完全一致时，才按紧急程度排序）

修正后SQL：

SELECT t.`id_ticket`,ts.`status_desc`, t.`data`, t.`client_name`, t.`email`, t.`number`, nu.`emergency_desc`, tp.`problem_desc`, t.`details`, t.`staff`,t.updated,u.name as selected,t.status_id,t.emergency_id,t.creator_staff
FROM ticket t
LEFT JOIN  user u on u.id=t.dtg
LEFT JOIN problem_type tp ON t.problem_type = tp.problem_id
LEFT JOIN urgent_level nu on t.urgent_id =nu.urgent_id
LEFT JOIN ticket_status ts on t.status_id=ts.status_id
Where vis=1 
ORDER BY t.status_id ASC, t.data DESC, t.urgent_id ASC
LIMIT 100

该写法适合t.data为带时分秒精度的DATETIME/TIMESTAMP类型的场景，这类场景下同时间点工单重复概率极低，不会打乱原有LIMIT截取逻辑，执行效率最高。

写法2：子查询实现（适配低精度日期字段） #

如果t.data字段仅存储到天（DATE类型，无时分秒），为避免同天工单因紧急度排序导致本该返回的新工单被挤出前100条，可以先通过子查询按原有逻辑取出100条目标工单，再在结果集内追加紧急度排序：

SELECT * FROM (
  SELECT t.`id_ticket`,ts.`status_desc`, t.`data`, t.`client_name`, t.`email`, t.`number`, nu.`emergency_desc`, tp.`problem_desc`, t.`details`, t.`staff`,t.updated,u.name as selected,t.status_id,t.emergency_id,t.creator_staff
  FROM ticket t
  LEFT JOIN  user u on u.id=t.dtg
  LEFT JOIN problem_type tp ON t.problem_type = tp.problem_id
  LEFT JOIN urgent_level nu on t.urgent_id =nu.urgent_id
  LEFT JOIN ticket_status ts on t.status_id=ts.status_id
  WHERE vis=1 
  ORDER BY t.status_id ASC, t.data DESC
  LIMIT 100
) AS sorted_tickets
ORDER BY sorted_tickets.status_id ASC, sorted_tickets.data DESC, sorted_tickets.urgent_id ASC

注：请提前确认urgent_id和紧急程度的映射关系符合升序预期，即紧急程度最高的Urgent对应的urgent_id值最小，如果映射关系相反，将排序规则里的ASC改为DESC即可。

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