表AA
----
ID
1-1
1-100
1-101
1-11
1-110
1-111
1-12
1-120
1-121排序结果ID
------
1-1
1-11
1-12
1-100
1-101
1-110
1-111
1-120
1-121
请教.
----
ID
1-1
1-100
1-101
1-11
1-110
1-111
1-12
1-120
1-121排序结果ID
------
1-1
1-11
1-12
1-100
1-101
1-110
1-111
1-120
1-121
请教.
order by substring(ID,1,1),substring(ID,3,1),substring(ID,4,1),substring(ID,5,1)
insert into @t select '1-1'
union all select '1-100'
union all select '1-101'
union all select '1-11'
union all select '1-110'
union all select '1-111'
union all select '1-12'
union all select '1-120'
union all select '1-121'select * from @t order by left(ID,charindex('-',ID)-1),cast(substring(ID,charindex('-',ID)+1,len(ID)) as int)
declare @T table(ID varchar(10))
insert into @t select '1-1'
union all select '1-100'
union all select '1-101'
union all select '1-11'
union all select '1-110'
union all select '1-111'
union all select '1-12'
union all select '1-120'
union all select '1-121'select * from @t order by cast(left(ID,charindex('-',ID)-1) as int),cast(substring(ID,charindex('-',ID)+1,len(ID)) as int)
order by cast(substring(ID,1,1) as int),cast(substring(ID,3,3) as int)
(id varchar(10)) insert into @a
select '1-1'
union all
select '1-100'
union all
select '1-101'
union all
select '1-11'
union all
select '1-110'
union all
select '1-111'
union all
select '1-12'
union all
select '1-120'
union all
select '1-121'select * from @a
order by right(id,len(id)-2)
select * from @a
order by cast(right(id,len(id)-2) as int)
这个才是
insert into @t select '1-1'
union all select '1-100'
union all select '1-101'
union all select '1-11'
union all select '1-110'
union all select '1-111'
union all select '1-12'
union all select '1-120'
union all select '1-121'select * from @T order by cast(right(ID,len(ID)-2) as int)