select name from yourtable order by name,len(name)
select * from 表 order by left(字段,patindex('%[0-9]%',字段+'0')-1) ,cast(stuff(字段,1,patindex('%[0-9]%',字段+'0')-1,'') as int)
--测试select * from( select col='aa1' union all select 'aa10' union all select 'aa2' union all select 'aa20' union all select 'r1' union all select 'r10' union all select 'r2' union all select 'r20' )a order by left(col,patindex('%[0-9]%',col+'0')-1) ,cast(stuff(col,1,patindex('%[0-9]%',col+'0')-1,'') as int)/*--测试结果col ---- aa1 aa2 aa10 aa20 r1 r2 r10 r20(所影响的行数为 8 行)--*/
select * from tb group by a order by left(a,1),datalength(a),a
--测试 create table tb(a varchar(10)) insert into tb select 'aa1' union all select 'aa10' union all select 'aa2' union all select 'aa20' union all select 'r1' union all select 'r10' union all select 'r2' union all select 'r20' --查询 select * from tb group by a order by left(a,1),datalength(a),a--删除测试表 drop table tb--结果:a ---------- aa1 aa2 aa10 aa20 r1 r2 r10 r20
zjcxc(邹建) () 信誉:399 select * from 表 order by left(字段,patindex('%[0-9]%',字段+'0')-1) ,cast(stuff(字段,1,patindex('%[0-9]%',字段+'0')-1,'') as int)
不行嘛?
order by left(字段,patindex('%[0-9]%',字段+'0')-1)
,cast(stuff(字段,1,patindex('%[0-9]%',字段+'0')-1,'') as int)
from(
select col='aa1'
union all select 'aa10'
union all select 'aa2'
union all select 'aa20'
union all select 'r1'
union all select 'r10'
union all select 'r2'
union all select 'r20'
)a order by left(col,patindex('%[0-9]%',col+'0')-1)
,cast(stuff(col,1,patindex('%[0-9]%',col+'0')-1,'') as int)/*--测试结果col
----
aa1
aa2
aa10
aa20
r1
r2
r10
r20(所影响的行数为 8 行)--*/
select *
from tb
group by a
order by left(a,1),datalength(a),a
create table tb(a varchar(10))
insert into tb
select 'aa1' union all
select 'aa10' union all
select 'aa2' union all
select 'aa20' union all
select 'r1' union all
select 'r10' union all
select 'r2' union all
select 'r20' --查询
select *
from tb
group by a
order by left(a,1),datalength(a),a--删除测试表
drop table tb--结果:a
----------
aa1
aa2
aa10
aa20
r1
r2
r10
r20
select * from 表
order by left(字段,patindex('%[0-9]%',字段+'0')-1)
,cast(stuff(字段,1,patindex('%[0-9]%',字段+'0')-1,'') as int)