日期:2014-05-18  浏览次数:20623 次

SQL排序问题
SQL排序问题:1-1-1,1-1-2,1-2-1,1-2-2
请问,这种字符串如何排序啊?
结果:1-1-1
   1-1-2
   1-2-1
   1-2-2

------解决方案--------------------
create table tempabc (a varchar(50))
insert into tempabc select '1-1-1 '
union all select '1-3-2 '
union all select '1-2-2 '
union all select '1-1-2 '
union all select '1-3-1 '
union all select '1-2-1 '
union all select '1-1-9 '

select * from tempabc order by a

drop table tempabc


(所影响的行数为 7 行)

a
--------------------------------------------------
1-1-1
1-1-2
1-1-9
1-2-1
1-2-2
1-3-1
1-3-2

(所影响的行数为 7 行)

------解决方案--------------------
--如果只是这样,可直接排序

create table tb(a varchar(10))
insert into tb values( '1-1-1 ')
insert into tb values( '1-1-2 ')
insert into tb values( '1-2-1 ')
insert into tb values( '1-2-2 ')
go

select * from tb order by a

drop table tb

/*
a
----------
1-1-1
1-1-2
1-2-1
1-2-2

(所影响的行数为 4 行)
*/
------解决方案--------------------
--如果位数不定.

create table tb(a varchar(10))
insert into tb values( '1-11-1 ')
insert into tb values( '1-1-2 ')
insert into tb values( '1-21-1 ')
insert into tb values( '1-20-2 ')
go

select cast(left(a,charindex( '- ',a) -1) as int) a1 , cast(substring(a,charindex( '- ',a) + 1 , charindex( '- ',a,charindex( '- ',a)+1) - charindex( '- ',a) -1) as int) a2,cast(left(reverse(a),charindex( '- ',reverse(a))-1) as int) a3
from tb
order by a1,a2,a3
drop table tb

/*
a1 a2 a3
----------- ----------- -----------
1 1 2
1 11 1
1 20 2
1 21 1

(所影响的行数为 4 行)
*/
------解决方案--------------------
--确保有 '- '号.

create table tb(a varchar(10))
insert into tb values( '1-11-1 ')
insert into tb values( '1-1-2 ')
insert into tb values( '1-21-1 ')
insert into tb values( '1-20-2 ')
go

select cast(left(a,charindex( '- ',a) -1) as int) a1 , cast(substring(a,charindex( '- ',a) + 1 , charindex( '- ',a,charindex( '- ',a)+1) - charindex( '- ',a) -1) as int) a2,cast(left(reverse(a),charindex( '- ',reverse(a))-1) as int) a3
from tb
where charindex( '- ',a)> 0
order by a1,a2,a3
drop table tb

/*
a1 a2 a3
----------- ----------- -----------
1 1 2
1 11 1
1 20 2
1 21 1

(所影响的行数为 4 行)
*/