如数据是
1
2
3
4
5
6
7
8
9
10
11
12
X1
X2
X3
X4
X5
X6
X7
X8
X9
X10
X11 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)的话,邹建的方法是行。但数据改为
"1 "
"2 "
"3 "
"4 "
"5 "
"6 "
"7 "
"8 "
"9 "
"10 "
"11 "
"12 "
"16 "
"19 "
"20 "
"21 "
"22 "
"25 "
"B1 "
"B2 "
"B3 "
"B4 "
"B5 "
"B6 "
"B7 "
"B8 "
"B9 "
"B10 "
"a11 "
"a12 "
"a13 "
"0 "
"101 "
"102 "
"110 "0
1
2
3
4
5
6
7
8
9
10
11
12
16
19
101
102
110
20
21
22
25
a13
a11
a12
B7
B8
B9
B1
B2
B3
B4
B5
B6
B10
测试后20排在101后!
1
2
3
4
5
6
7
8
9
10
11
12
X1
X2
X3
X4
X5
X6
X7
X8
X9
X10
X11 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)的话,邹建的方法是行。但数据改为
"1 "
"2 "
"3 "
"4 "
"5 "
"6 "
"7 "
"8 "
"9 "
"10 "
"11 "
"12 "
"16 "
"19 "
"20 "
"21 "
"22 "
"25 "
"B1 "
"B2 "
"B3 "
"B4 "
"B5 "
"B6 "
"B7 "
"B8 "
"B9 "
"B10 "
"a11 "
"a12 "
"a13 "
"0 "
"101 "
"102 "
"110 "0
1
2
3
4
5
6
7
8
9
10
11
12
16
19
101
102
110
20
21
22
25
a13
a11
a12
B7
B8
B9
B1
B2
B3
B4
B5
B6
B10
测试后20排在101后!
insert @t select
'1'
union all select
'2'
union all select
'3'
union all select
'4'
union all select
'5'
union all select
'6'
union all select
'7'
union all select
'8'
union all select
'9'
union all select
'10'
union all select
'11'
union all select
'12'
union all select
'16'
union all select
'19'
union all select
'20'
union all select
'21'
union all select
'22'
union all select
'25'
union all select
'B1'
union all select
'B2'
union all select
'B3'
union all select
'B4'
union all select
'B5'
union all select
'B6'
union all select
'B7'
union all select
'B8'
union all select
'B9'
union all select
'B10'
union all select
'a11'
union all select
'a12'
union all select
'a13'
union all select
'0'
union all select
'101'
union all select
'102'
union all select
'110'select *
from @t
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)
patindex('%[^0-9]%',reverse(rtrim(id))+'a')+1