请问一字符串排序问题
我的表中用货号和货位两个字段,
货位的格式为: "X-XX-XX "(第一位为字母,后面的均为数字),如:
A-01-01
A-01-02
A-02-01
B-01-01
B-01-02
B-02-03
....
...
...
Z-09-04
Z-09-05
现要求按货位排序,
A开头的从大到小排,B开头的按从小到大排,
C开头的从大到小排,D开头的按从小到大排,
E开头的从大到小排,F开头的按从小到大排....
....
....
一共26个字母,
请问如果我的表中有N条记录,该如何实现这样的排序?
[解决办法]
group by case ASCII(left(货位,1))%2 =1 then 货位 else 货位 desc end
[解决办法]
declare @t table(货位 varchar(20))
insert @t select
'A-01-01 '
union all select
'A-01-02 '
union all select
'A-02-01 '
union all select
'B-01-01 '
union all select
'B-01-02 '
union all select
'B-02-03 '
union all select
'Y-01-03 '
union all select
'Y-02-03 '
union all select
'Z-01-03 '
union all select
'Z-02-03 '
select * from @t
order by left(货位,1),
case when (ASCII(left(货位,1))-ASCII( 'A ')) % 2 =0 then stuff(货位,1,1, ' ') end desc,
case when (ASCII(left(货位,1))-ASCII( 'A ')) % 2 =1 then stuff(货位,1,1, ' ') end asc
