如何对混合多级章节编号与文本的数据库列进行自然排序？

问题场景 #

数据库表myTable的Description列混合了层级不固定的章节编号（如1、1.1、1.1.1）和纯文本内容，示例数据如下：

+-----------------+
| Description     |
+-----------------+
|  1.1 First      |
|  1.2 Second     |
|  1.3 Third      |
|  1.10 Tenth     |
|  1.11 Eleventh  |
|  1.20 Twentieth |
|  Unnumbered One |
|  Unnumbered Two |
+-----------------+

直接执行SELECT * FROM myTable ORDER BY Description会得到不符合逻辑的排序结果（1.10排在1.2之前），期望的排序顺序是按章节编号的数值层级排序，纯文本内容放在最后。

解决方案 #

由于章节层级不固定，无法通过简单的类型转换实现通用排序，以下是主流数据库的针对性方案：

MySQL #

通过正则匹配区分带编号的行，拆分章节编号的每一层并转换为整数排序：

SELECT *
FROM myTable
ORDER BY
  -- 带章节编号的行优先排序，纯文本后置
  CASE WHEN Description REGEXP '^[0-9]+(\\.[0-9]+)* ' THEN 0 ELSE 1 END,
  -- 拆分章节编号的各层级，转换为无符号整数
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(Description, ' ', 1), '.', 1) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(Description, ' ', 1), '.', 2) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(Description, ' ', 1), '.', 3) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(Description, ' ', 1), '.', 4) AS UNSIGNED),
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(Description, ' ', 1), '.', 5) AS UNSIGNED),
  Description;

说明：这里假设最多5层级，若实际层级更多，可继续添加对应的拆分语句；正则表达式可根据实际格式调整（比如处理开头的空格）。

SQL Server #

利用XML将章节编号拆分为数值节点，按节点值排序：

SELECT *
FROM myTable
ORDER BY
  CASE WHEN Description LIKE '[0-9]%[0-9] ' THEN 0 ELSE 1 END,
  -- 提取章节编号并转换为XML节点，依次获取各层级数值
  (SELECT CAST('<n>' + REPLACE(LTRIM(SUBSTRING(Description, 1, CHARINDEX(' ', Description)-1)), '.', '</n><n>') + '</n>' AS XML).value('(/n)[1]', 'INT')),
  (SELECT CAST('<n>' + REPLACE(LTRIM(SUBSTRING(Description, 1, CHARINDEX(' ', Description)-1)), '.', '</n><n>') + '</n>' AS XML).value('(/n)[2]', 'INT')),
  (SELECT CAST('<n>' + REPLACE(LTRIM(SUBSTRING(Description, 1, CHARINDEX(' ', Description)-1)), '.', '</n><n>') + '</n>' AS XML).value('(/n)[3]', 'INT')),
  (SELECT CAST('<n>' + REPLACE(LTRIM(SUBSTRING(Description, 1, CHARINDEX(' ', Description)-1)), '.', '</n><n>') + '</n>' AS XML).value('(/n)[4]', 'INT')),
  Description;

说明：LTRIM用于处理开头的空格，若编号格式无前置空格可省略；同样可根据层级需求扩展节点提取语句。

PostgreSQL #

利用数组排序特性，将章节编号转换为整数数组直接排序：

SELECT *
FROM myTable
ORDER BY
  CASE WHEN Description ~ '^[0-9]+(\.[0-9]+)* ' THEN 0 ELSE 1 END,
  -- 将章节编号拆分为整数数组，数组会按元素逐一比较排序
  string_to_array(split_part(LTRIM(Description), ' ', 1), '.')::INT[],
  Description;

说明：PostgreSQL支持整数数组的自然排序，无需手动拆分每一层，层级数量不固定的场景下最简洁。

注意事项 #

• 若章节编号与文本的分隔符不是单个空格，需调整字符串处理函数中的分隔符参数；
• 纯文本的排序顺序可根据需求修改ORDER BY的最后一个字段；
• 正则表达式可根据实际数据格式优化，比如处理编号前后的特殊字符。

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