MySQL含冒号列按冒号前数值降序排序的实现方法

问题核心是直接对数字:数字格式的列做字符串排序会遵循字典序，无法满足按冒号前数值降序的需求，需要提取冒号前的子串并转换为数值类型后再排序。以下是不同SQL数据库的实现方案：

MySQL/MariaDB #

使用SUBSTRING_INDEX截取冒号前的部分，转换为无符号整数排序：

SELECT * FROM test ORDER BY CAST(SUBSTRING_INDEX(column123, ':', 1) AS UNSIGNED) DESC;

PostgreSQL #

用SPLIT_PART拆分字符串后转整数，或通过正则匹配提取前缀：

-- 方法1：SPLIT_PART拆分
SELECT * FROM test ORDER BY (SPLIT_PART(column123, ':', 1))::INTEGER DESC;

-- 方法2：正则匹配前缀
SELECT * FROM test ORDER BY (SUBSTRING(column123 FROM '^\\d+'))::INTEGER DESC;

SQL Server #

通过CHARINDEX定位冒号位置，再用SUBSTRING提取前缀并转整数：

SELECT * FROM test ORDER BY CAST(SUBSTRING(column123, 1, CHARINDEX(':', column123) - 1) AS INT) DESC;

Oracle #

用INSTR找到冒号位置，SUBSTR提取前缀后转数值：

SELECT * FROM test ORDER BY TO_NUMBER(SUBSTR(column123, 1, INSTR(column123, ':') - 1)) DESC;

补充说明 #

如果冒号前包含负整数，只需将转换类型调整为对应有符号数值类型（比如MySQL用SIGNED，PostgreSQL用BIGINT等）即可。

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