ORDER BY(VARCHAR)和ORDER BY TO\ U NUMBER(VARCHAR)之间的差异

string/varchar的排序(比较)不同于数字的排序(比较)。

2<11 ,在字符串/varchar中 "2">"11"

假设您对ASC进行排序,如果您不转换为数字,则在id=“11”的行后面有一个带“2”的行,如果您转换为数字,则反之亦然

字符串排序更为复杂,数据库可以使用各种方法进行排序。 https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm

with src as (
select '1' x from dual
union 
select '2' from dual
union
select '10' from dual)
select x from src order by x;


with src as (
select '1' x from dual
union 
select '2' from dual
union
select '10' from dual)
select x from src order by to_number(x);

lpad解决方案

with src as (
select '1' x from dual
union 
select '2' from dual
union
select '10' from dual)
select x from src order by lpad(x,10,'0');