Question

sql 2005 怎么修改表中字段默认排序规则？

有表a , 字段c 值为2R，5K，1M。order by c 后系统默认为：1m,2r,5k 正确的排序为：2R，5K，1M
换算关系为：1000R=1K,1000K=1M ，谢谢。

Answer 1

select
c
from
a
order by
case when right(c,1)='R' then cast(left(c,len(c)-1) as int)
when right(c,1)='K' then cast(left(c,len(c)-1) as int)*1000
when right(c,1)='M' then cast(left(c,len(c)-1) as int)*1000*1000 end;

追问

非常谢谢你，我问题没有提好，还有一种情况，后面还有数，比如2K40，1K5，20M50。

追答

　　select
　　c
　　from
　　a
　　order by
　　case when charindex('M',c)>0 and charindex('K',c)>0 and len(c)>charindex('K',c) then
　　cast(substring(c,1,charindex('M',c)-1) as int)*1000*1000+
　　cast(substring(c,charindex('M',c)+1,charindex('K',c)-charindex('M',c)-1 as int)*1000+
　　cast(substring(c,charindex('K',c)+1) as int)
　　when charindex('M',c)>0 and charindex('K",c)>0 then
　　cast(substring(c,1,charindex('M',c)-1) as int)*1000*1000+
　　cast(substring(c,charindex('M',c)+1,charindex('K',c)-charindex('M',c)-1 as int)*1000
　　when charindex('K',c)>0 and len(c)>charindex('K',c) then
　　cast(substring(c,1,charindex('K',c)-1) as int)*1000+
　　cast(substring(c,charindex('K',c)+1) as int)
　　when charindex('M',c)>0 then
　　cast(substring(c,1,charindex('M',c)-1) as int)*1000*1000

　　when charindex('K',c)>0 then
　　cast(left(c,len(c)-1) as int)*1000
　　when charindex('R',c)>0 then
　　cast(left(c,len(c)-1) as int)
　　end