我正在尝试按数据库中的数字列进行排序,该列的值为 1-999
I’m attempting to order by a number column in my database which has values 1-999
当我使用
ORDER_BY registration_no ASC我明白了……
1 101 102 103 104 105 106 107 108 109 11 110 Etc…所以它似乎是按第一个数字排序,而不是数字.
So it appears to be ordering by the first digit as oppose to the number.
如果我想按值排序,有人知道要使用什么 SQL 吗?所以 1,2,3,4,5,6 等等
Does anyone know what SQL to use if I want to order this by value? So 1,2,3,4,5,6 etc
推荐答案一种按正整数排序的方法,当它们存储为 varchar 时,是先按长度排序,然后按值排序:
One way to order by positive integers, when they are stored as varchar, is to order by the length first and then the value:
order by len(registration_no), registration_no这在列可能包含非数字值时特别有用.
This is particularly useful when the column might contain non-numeric values.
注意:在某些数据库中,获取字符串长度的函数可能被称为length() 而不是len().
Note: in some databases, the function to get the length of a string might be called length() instead of len().
更多推荐
用于按数字排序的 SQL
发布评论