select * from 表 order by charindex('%[0-9]%',字段) desc,cast(substring(字段,charindex('%[0-9]%',字段),len(字段)) as int)
select * from table1 order by cast(cast(id as char)as int)
select * from 表 order by left(id,len(id)-patindex('%[^0-9]]',reverse(rtrim(id))+'a')-1) ,cast(stuff(id,1,len(id)-patindex('%[^0-9]]',reverse(rtrim(id))+'a')-1,'') as float)
select * from table order by case left(id,1) when 'x' then id else convert(int,id) end
from 表
order by charindex('%[0-9]%',字段) desc,cast(substring(字段,charindex('%[0-9]%',字段),len(字段)) as int)
order by left(id,len(id)-patindex('%[^0-9]]',reverse(rtrim(id))+'a')-1)
,cast(stuff(id,1,len(id)-patindex('%[^0-9]]',reverse(rtrim(id))+'a')-1,'') as float)
其它的只能针对以上数据,换个玩法就不好使了,比如:
1
2
3
4
5
6
7
8
9
10
11
12
13
D2
D3
D4
D5
D6
DT1
T2
T3
T4
T5
T6
X1
X2
X3
X4
X5
X6
再换个难度的,有兴趣的一试1
2
3
4
5
6
7
8
9
10
11
12
13
DT1
D2
D3
D4
D5
D6
T2
T3
T4
T5
T6
X1
X2
X3
X4
X5
X6