declare @s as varchar(2000) set @s='BR13-2017,BR13-2018,CR12-0002'select top 2000 id=identity(int,1,1) into # from syscolumns a,syscolumns b select A=substring(@s,b.id,charindex(',',@s+',',b.id)-b.id) from # b where substring(','+@s,b.id,1)=',' order by b.id drop table #
select * from tablename order by Left(产品编号, 2) desc, substring(产品编号, 3,len(产品编号) - 2) desc
SELECT * FROM 表 ORDER by LEFT(产品编号, 2), substring(产品编号, 3,2) DESC,RIGHT(len(产品编号,4) desc
select * from 表 order by left(字段,2) ,convert(int,replace(right(字段,7),'-',''))
create table tb ( col1 varchar(50) ) insert tb select 'BR13-2017' union all select 'CR12-0002' union all select 'EY13-2017' union all select 'ey12-0002' union all select 'TY13-2097' union all select 'Xy12-0402' union all select 'BR13-2018' select * from tb order by left(col1,2) ,convert(int,replace(right(col1,7),'-',''))drop table tb(所影响的行数为 7 行)col1 -------------------------------------------------- BR13-2017 BR13-2018 CR12-0002 ey12-0002 EY13-2017 TY13-2097 Xy12-0402(所影响的行数为 7 行)
set @s='BR13-2017,BR13-2018,CR12-0002'select top 2000 id=identity(int,1,1) into # from syscolumns a,syscolumns b
select
A=substring(@s,b.id,charindex(',',@s+',',b.id)-b.id)
from # b
where substring(','+@s,b.id,1)=','
order by b.id
drop table #
order by left(字段,2)
,convert(int,replace(right(字段,7),'-',''))
(
col1 varchar(50)
)
insert tb
select 'BR13-2017' union all
select 'CR12-0002' union all
select 'EY13-2017' union all
select 'ey12-0002' union all
select 'TY13-2097' union all
select 'Xy12-0402' union all
select 'BR13-2018'
select * from tb order by left(col1,2) ,convert(int,replace(right(col1,7),'-',''))drop table tb(所影响的行数为 7 行)col1
--------------------------------------------------
BR13-2017
BR13-2018
CR12-0002
ey12-0002
EY13-2017
TY13-2097
Xy12-0402(所影响的行数为 7 行)