A-1
A-11
A-11A
A-11B
A-20
A-6
C-2
C-4
C-6
D-2
G-1
K-100
LB-1
M-10
M-100
有没有办法先按前面的字母排序,然后再按后面的数字排序呢。
A-11
A-11A
A-11B
A-20
A-6
C-2
C-4
C-6
D-2
G-1
K-100
LB-1
M-10
M-100
有没有办法先按前面的字母排序,然后再按后面的数字排序呢。
--a是字段名
select * from biao
order by left(a,charindex('-',a)-1),stuff(a,1,charindex('-',a),'')
insert @a select 'A-1'
union all select 'A-11'
union all select 'A-11A'
union all select 'A-11B'
union all select 'A-20'
union all select 'A-6'
union all select 'C-2'
union all select 'C-4'
union all select 'C-6'
union all select 'D-2'
union all select 'G-1'
union all select 'K-100'
union all select 'LB-1'
union all select 'M-10'
union all select 'M-100'select * from @a order by case when left(a,charindex('-',a)-1) like '[A-Z]%' then '' else right(a,len(a)-charindex('-',a)) end
还是A-1,A-11,A-11A,A-11B,A-2?
order by left(a,charindex('-',a)-1),substring(a,charindex('-',a)+1,1),
substring(a,charindex('-',a)+2,1),substring(a,charindex('-',a)+3,1)
declare @a table(a varchar(10))
insert @a select 'A-1'
union all select 'A-11'
union all select 'A-11A'
union all select 'A-11B'
union all select 'A-20'
union all select 'A-6'
union all select 'C-2'
union all select 'C-4'
union all select 'C-6'
union all select 'D-2'
union all select 'G-1'
union all select 'K-100'
union all select 'LB-1'
union all select 'M-10'
union all select 'M-100'select * from (select distinct top 100 percent left(a,charindex('-',a)-1) b from @a) c Left Join @a d on c.b=left(a,charindex('-',a)-1) order by c.b,cast(left(right(a,len(a)-charindex('-',a)),case when patindex('%[^0-9]%',right(a,len(a)-charindex('-',a)))=0 then right(a,len(a)-charindex('-',a)) else patindex('%[^0-9]%',right(a,len(a)-charindex('-',a)))-1 end) as int)
(所影响的行数为 15 行)b a
---------- ----------
A A-1
A A-6
A A-11
A A-11A
A A-11B
A A-20
C C-2
C C-4
C C-6
D D-2
G G-1
K K-100
LB LB-1
M M-10
M M-100(所影响的行数为 15 行)