SQL如何对带非数值格式的varchar类型age列使用ORDER BY排序

现有一张表包含age字段，该字段为varchar类型，因为1岁以下人员的年龄以月份为单位存储，末尾附加m字符（9个月大对应存储为'9m'）。

我知道这种设计通常并不合理，更优方案是存储出生日期，但本场景下的年龄是指某一特定历史日期的年龄，同时这也是课程内容的一部分，核心目标是学习如何处理「特殊」数据。

我的第一个思路是给所有非纯数值的年龄添加前导零，编写的SQL语句如下：

SELECT * 
FROM db 
ORDER BY REPLACE(age, (IF ISNUMERIC(age) age ELSE CONCAT('0', age))) DESC;

但这条SQL语句并不合法，我尝试的其他写法也都无法运行。我想要解决的问题是：如何在不修改数据库的前提下，调整ORDER BY用到的排序值实现正确排序？

我想到的另一个方案是分别查询纯数值age的行和非纯数值age的行，分别排序后再合并，我写的语句如下：

(SELECT name, age 
 FROM titanic 
 WHERE ISNUMERIC(age) 
 ORDER BY age DESC) 
UNION 
(SELECT name, age 
 FROM titanic 
 WHERE NOT ISNUMERIC(age) 
 ORDER BY age);

这条语句是合法的，也能返回结果，但最终结果的排序不符合预期，看起来UNION操作打乱了之前的排序结果。

希望能得到相关解决方案指引，或是告知我需要学习的相关函数/方法名称，非常感谢！

核心问题说明 #

1. 第一条SQL报错是因为IF函数语法使用错误，同时REPLACE的调用逻辑不符合需求。
2. 第二条SQL排序失效是因为SQL规范中，子查询的ORDER BY如果没有搭配LIMIT，会被数据库优化器直接忽略，且UNION默认会执行去重操作，也会打乱原有子查询的顺序。

最优实现方案 #

直接在ORDER BY子句中把所有年龄统一转换为总月份数作为排序依据，数值类型的年龄（岁）乘以12转换为月份，带m的年龄直接提取数字作为月份，不需要修改表结构，也不需要拆分查询。

以下是MySQL环境的示例写法：

SELECT * 
FROM titanic
ORDER BY 
  CASE 
    -- 纯数值年龄转总月份
    WHEN age REGEXP '^[0-9]+$' THEN CAST(age AS UNSIGNED) * 12
    -- 带m的年龄提取月份数
    ELSE CAST(TRIM(TRAILING 'm' FROM age) AS UNSIGNED)
  END DESC;

如果是SQL Server环境，可以把正则判断替换为ISNUMERIC函数：

SELECT * 
FROM titanic
ORDER BY 
  CASE 
    WHEN ISNUMERIC(age) = 1 THEN CAST(age AS INT) * 12
    ELSE CAST(LEFT(age, LEN(age)-1) AS INT)
  END DESC;

UNION方案的修正写法 #

如果一定要用拆分查询的思路，需要在外层加统一排序逻辑，同时用UNION ALL避免去重：

SELECT name, age 
FROM (
  SELECT name, age, 
    CAST(age AS UNSIGNED) AS sort_val,
    1 AS sort_priority
  FROM titanic 
  WHERE age REGEXP '^[0-9]+$'
  UNION ALL
  SELECT name, age, 
    CAST(TRIM(TRAILING 'm' FROM age) AS UNSIGNED)/12 AS sort_val,
    2 AS sort_priority
  FROM titanic 
  WHERE age NOT REGEXP '^[0-9]+$'
) t
ORDER BY sort_val DESC, sort_priority ASC;

• CASE条件判断语句、IF函数的正确语法
• 字符串处理函数：正则匹配（REGEXP）、内容裁剪（TRIM/LEFT）、长度计算（LEN）等
• 数据类型转换函数：CAST/CONVERT
• UNION与UNION ALL的差异、子查询排序的生效规则
• ORDER BY自定义排序键的使用方法

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