如何在SQL中按指定规则对混合字符型section列排序？

核心思路 #

先将带前导零的纯数字排在最靠前位置，接着按数字实际数值大小排序剩余纯数字项，最后按字符串自然顺序排列字母开头的项。

排序语句 #

ORDER BY
  -- 优先排列带前导零的纯数字
  a.section REGEXP '^0[0-9]+$' DESC,
  -- 区分纯数字与非数字，纯数字项前置
  a.section REGEXP '^[0-9]+$' DESC,
  -- 纯数字按实际数值大小排序
  CAST(a.section AS UNSIGNED),
  -- 非数字项按字符串规则排序
  a.section

效果验证 #

用你的测试数据执行后，会得到期望的排序结果：

0057
0058
5
6
10
11
A5
A6

语句说明 #

• a.section REGEXP '^0[0-9]+$' DESC：匹配以0开头且后续全为数字的项，确保这类数据排在最前面
• a.section REGEXP '^[0-9]+$' DESC：将剩余纯数字项排在非数字项之前
• CAST(a.section AS UNSIGNED)：把纯数字字符串转为无符号整数，按实际数值排序，保证5、6排在10、11之前
• a.section：对非数字项采用字符串默认排序规则

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