--测试数据 declare @t table(col varchar(10)) insert @t select 'YU-1244' union all select 'YU-1254' union all select 'YU-1248' union all select 'YU-1294' union all select 'AC-231' union all select 'AC-235' union all select 'AC-2481' union all select 'AC-239' --查询 select * from @t order by col,cast(stuff(col,1,charindex('-',col),'') as int)/*--测试结果col ---------- AC-231 AC-235 AC-239 AC-2481 YU-1244 YU-1248 YU-1254 YU-1294(所影响的行数为 8 行) --*/
select * from 表 order by 字段,val(mid(字段,instr(字段,"-")+1))
在SQL里直接写select * from table order by field就可以了。我在Win+SQL2K下试验过了。
declare @t table(col varchar(10))
insert @t select 'YU-1244'
union all select 'YU-1254'
union all select 'YU-1248'
union all select 'YU-1294'
union all select 'AC-231'
union all select 'AC-235'
union all select 'AC-2481'
union all select 'AC-239' --查询
select * from @t
order by col,cast(stuff(col,1,charindex('-',col),'') as int)/*--测试结果col
----------
AC-231
AC-235
AC-239
AC-2481
YU-1244
YU-1248
YU-1254
YU-1294(所影响的行数为 8 行)
--*/
order by 字段,val(mid(字段,instr(字段,"-")+1))