1、问题描述
我写一个sql想要把查询结果根据LineNumber升序进行排序,即1.0,1.1,1.2,...1.3.2....2.0,......10.0,......15.2,......这样子进行排序的。但是排序结果总是不对。
select POID, LineNumber from Business..PurchaseOrderItem where POID=131089 order by LineNumber asc
2、产生原因
LineNumber字段是varchar类型的,varchar比较是先比较首个字符的ascii码的大小,然后再依次比较其他的字符的ascii码。
比如对于"10.0"和"2.0","10.0"的首个字符为"1","2.0"的首个字符为"2"。"1"<"2",因此"10.0"是比"2.0"要小,"10.0"是排在"2.0"前面的。
3、解决方法
把varchar转为数字类型进行比较。因为LineNumber会有像"6.3.2"这种是无法直接转为数字的。所以我这里就将第一个小数点左侧的转为数字。先比较第一个小数点左侧的数字,再比较整体。
--根据LineNumber进行升序排序
select POID, LineNumber from Business..PurchaseOrderItem where POID=131089
order by
case when SubString (LineNumber, 0, CharIndex ('.', LineNumber)) ='' then
Cast(LineNumber as bigint)
else
Cast(SubString (LineNumber, 0, CharIndex ('.', LineNumber)) as bigint)
end ,
--LineNumber为空或者null的,放在最后
case when LineNumber='' or LineNumber is null then 1 else 0 end asc,LineNumber asc
解释一下这段代码的意思:因为LineNumber可能没有小数点,比如LineNumber=12,因此 SubString (LineNumber, 0, CharIndex ('.', LineNumber))取第一个小数点左侧数字就为空了。因此我就直接把LineNumber转为数字类型进行排序比较。
4、总结
无