讲明白了,就好做了: declare @a table (Number varchar(10)) insert @a select '10' union all select '5' union all select '20' union all select '10A' union all select '30'select * from @a order by cast((case when isnumeric(number)=1 then number else left(number,len(number)-1) end) as int),numberNumber ---------- 5 10 10A 20 30
select * from 表 order by left(number,1)
select * from 表 order by left(number,1),substring(number,2,1),substring(number,3,1)
select * from table order by cast(left(number,PATINDEX('%[^0-9]%','0'+number+'a')-2)as int)
declare @table table(number char(10)) insert into @table select '10' union select '5' union select '20' union select '10A' union select '19A75' union select '10' union select '1034z' union select 'A' union select '' select * from @table order by cast(left(number,PATINDEX('%[^0-9]%','0'+number+'a')-2)as int)结果 (空) A 5 10 10A 19A75 20 1034z
我的语句会报错,有没有办法把字符分拆出去?
比如 10
10 a
如果能,则select * from table order by convert(int,feilds1),feilds2
如果有类似 'a1w23'这样的字符出现的话,类型转换根本没用,而且也没办法把这些东西与数字一起排序:你说100与'abc'谁大?(按数值排序)
如果数字,字符分开排序的话,先分别用select语句order by一下,再用union合起来就是...
declare @a table (Number varchar(10))
insert @a select '10'
union all select '5'
union all select '20'
union all select '10A'
union all select '30'select * from @a order by cast((case when isnumeric(number)=1 then number else left(number,len(number)-1) end) as int),numberNumber
----------
5
10
10A
20
30
from table
order by cast(left(number,PATINDEX('%[^0-9]%','0'+number+'a')-2)as int)
insert into @table
select '10'
union
select '5'
union
select '20'
union
select '10A'
union
select '19A75'
union
select '10'
union
select '1034z'
union
select 'A'
union
select '' select *
from @table
order by cast(left(number,PATINDEX('%[^0-9]%','0'+number+'a')-2)as int)结果
(空)
A
5
10
10A
19A75
20
1034z