Oracle中带字符串拼接的ORDER BY排序异常问题求助

你的排序失效大概率是因为pre_number字段的数据类型是字符串（比如VARCHAR），而非数值类型（INT、NUMERIC等）。字符串排序是按字符的ASCII码顺序比较，比如'10'会排在'2'前面，看起来就像是随机排序。

解决办法 #

方法1：修改字段类型（推荐） #

如果业务允许，直接把pre_number字段改成数值类型（比如INT），之后直接用order by pre_number就能正常按数值排序。

方法2：排序时转换字段类型 #

如果不能修改字段类型，在排序时将字符串转成数值类型再排序，不同数据库的写法如下：

MySQL/MariaDB

select distinct NAME ||'--'|| pre_number as VALUE
from ftcon
where name ='ABC' and status = 'ACTIVE'
order by cast(pre_number as unsigned);

或者更简洁的写法：

select distinct NAME ||'--'|| pre_number as VALUE
from ftcon
where name ='ABC' and status = 'ACTIVE'
order by pre_number + 0;

Oracle

select distinct NAME ||'--'|| pre_number as VALUE
from ftcon
where name ='ABC' and status = 'ACTIVE'
order by to_number(pre_number);

SQL Server

select distinct NAME + '--' + pre_number as VALUE
from ftcon
where name ='ABC' and status = 'ACTIVE'
order by convert(int, pre_number);

额外注意 #

原SQL中的distinct是针对拼接后的VALUE字段去重，转换pre_number的类型不会影响去重逻辑，放心使用。

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