用于按数字排序的 SQL - 1,2,3,4 等而不是 1,10,11,12
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16519648/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL for ordering by number - 1,2,3,4 etc instead of 1,10,11,12
提问by Konnor262
I'm attempting to order by a number column in my database which has values 1-999
我正在尝试按数据库中的数字列进行排序，该列的值为 1-999
When I use
当我使用
ORDER_BY registration_no ASC
I get….
我明白了……
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.
所以它似乎是按第一位数字排序，而不是数字。
Does anyone know what SQL to use if I want to order this by value? So 1,2,3,4,5,6etc
如果我想按值排序，有谁知道要使用什么 SQL？所以，1,2,3,4,5,6等
回答by Gordon Linoff
One way to order by positive integers, when they are stored as varchar, is to order by the length first and then the value:
当正整数存储为 时varchar，一种按正整数排序的方法是先按长度排序，然后按值排序：
order by len(registration_no), registration_no
This is particularly useful when the column might contain non-numeric values.
当列可能包含非数字值时，这特别有用。
Note: in some databases, the function to get the length of a string might be called length()instead of len().
注意：在某些数据库中，可能会调用获取字符串长度的函数length()而不是len().
回答by juergen d
ORDER_BY cast(registration_no as unsigned) ASC
explicitly converts the value to a number. Another possibility to achieve the same would be
显式地将值转换为数字。实现相同目标的另一种可能性是
ORDER_BY registration_no + 0 ASC
which will force an implicit conversation.
这将强制进行隐式对话。
Actually you should check the table definition and change it. You can change the data type to intlike this
实际上，您应该检查表定义并进行更改。您可以将数据类型更改为int这样
ALTER TABLE your_table MODIFY COLUMN registration_no int;
回答by IPOSTEDONCE
If you are using SQL Server:
如果您使用的是 SQL Server：
ORDER_BY cast(registration_no as int) ASC
回答by priyanka
ORDER_BY cast(registration_no as unsigned) ASC
gives the desired result with warnings.
给出带有警告的预期结果。
Hence, better to go for
因此，最好去
ORDER_BY registration_no + 0 ASC
for a clean result without any SQL warnings.
对于没有任何 SQL 警告的干净结果。
回答by priyanka
I assume your column type is STRING (CHAR, VARCHAR, etc) and sorting procedure is sorting it as a string. What you need to do is to convert value into numeric value. How to do it will depend on SQL system you use.
我假设您的列类型是 STRING（CHAR、VARCHAR 等），并且排序过程将其作为字符串进行排序。您需要做的是将值转换为数值。如何做将取决于您使用的 SQL 系统。
回答by SQLCodingIsFunnn
I prefer doing a "PAD" to the data. MySql calls it LPAD, but you can work your way around to doing the same thing in SQL Server.
我更喜欢对数据做一个“PAD”。MySql 将其称为 LPAD，但您可以通过自己的方式在 SQL Server 中执行相同的操作。
ORDER BY REPLACE(STR(ColName, 3), SPACE(1), '0')
This formula will provide leading zeroes based on the Column's length of 3. This functionality is very useful in other situations outside of ORDER BY, so that is why I wanted to provide this option.
这个公式将提供基于列长度 3 的前导零。这个功能在 ORDER BY 之外的其他情况下非常有用，所以这就是我想提供这个选项的原因。
Results: 1 becomes 001, and 10 becomes 010, while 100 remains the same.
结果：1变成001，10变成010，100不变。
回答by Joey Morgan
Sometimes you just don't have a choice about having to store numbers mixed with text. In one of our applications, the web site host we use for our e-commerce site makes filters dynamically out of lists. There is no option to sort by any field but the displayed text. When we wanted filters built off a list that said things like 2" to 8" 9" to 12" 13" to 15" etc, we needed it to sort 2-9-13, not 13-2-9 as it will when reading the numeric values. So I used the SQL Server Replicate function along with the length of the longest number to pad any shorter numbers with a leading space. Now 20 is sorted after 3, and so on.
有时您只是没有选择必须存储与文本混合的数字。在我们的一个应用程序中，我们用于电子商务网站的网站主机动态地从列表中筛选出过滤器。除了显示的文本外，没有选项可以按任何字段排序。当我们希望过滤器建立在一个列表中，比如 2" 到 8" 9" 到 12" 13" 到 15" 等，我们需要它来排序 2-9-13，而不是 13-2-9，因为它会在读取数值。因此，我使用 SQL Server Replicate 函数以及最长数字的长度来用前导空格填充任何较短的数字。现在 20 排在 3 之后，依此类推。
I was working with a view that gave me the minimum and maximum lengths, widths, etc for the item type and class, and here is an example of how I did the text. (LBnLow and LBnHigh are the Low and High end of the 5 length brackets.)
我正在使用一个视图，该视图为我提供了项目类型和类别的最小和最大长度、宽度等，这里是我如何处理文本的示例。（LB nLow 和 LB nHigh 是 5 个长度括号的低端和高端。）
REPLICATE(' ', LEN(LB5Low) - LEN(LB1High)) + CONVERT(NVARCHAR(4), LB1High) + '" and Under' AS L1Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB2Low)) + CONVERT(NVARCHAR(4), LB2Low) + '" to ' + CONVERT(NVARCHAR(4), LB2High) + '"' AS L2Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB3Low)) + CONVERT(NVARCHAR(4), LB3Low) + '" to ' + CONVERT(NVARCHAR(4), LB3High) + '"' AS L3Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB4Low)) + CONVERT(NVARCHAR(4), LB4Low) + '" to ' + CONVERT(NVARCHAR(4), LB4High) + '"' AS L4Text,
CONVERT(NVARCHAR(4), LB5Low) + '" and Over' AS L5Text
回答by Dev Rathi
This problem is just because you have declared the column in CHAR, VARCHAR or TEXT datatype. Just change the datatype to INT, BIGINT etc. This is will solved the problem of your custom ordering.
这个问题只是因为您已将列声明为 CHAR、VARCHAR 或 TEXT 数据类型。只需将数据类型更改为 INT、BIGINT 等。这将解决您的自定义排序问题。
