SQL ORDER 字符以数字表示
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/489874/
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 ORDER chars numerically
提问by T.T.T.
I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following:
我有一列数字存储为字符。当我为此列执行 ORDER BY 时，我得到以下信息：
100
131
200
21
30
31000
etc.
100
131
200
21
30
31000
等
How can I order these chars numerically? Do I need to convert something or is there already an SQL command or function for this?
如何按数字顺序排列这些字符？我是否需要转换某些内容，或者是否已经有 SQL 命令或函数用于此目的？
Thank You.
谢谢你。
回答by Ray Hidayat
Try this:
尝试这个：
ORDER BY CAST(thecolumn AS int)
回答by Nev
This Worked for me:
这对我有用：
ORDER BY ABS(column_name)
回答by BenAlabaster
This is an issue with ordering numeric strings in a "natural sort" (if you lookup "natural sorting" on Google you'll find tons of stuff). Essentially casting the string as int and sorting on the resulting value should fix it.
这是在“自然排序”中对数字字符串进行排序的问题（如果您在 Google 上查找“自然排序”，您会发现大量内容）。本质上将字符串转换为 int 并对结果值进行排序应该可以修复它。
回答by John
The reason for this is that with a char data type, you are sorting the rows as a string.
这样做的原因是，对于 char 数据类型，您将行作为字符串进行排序。
The idea to ORDER BY CAST()is correct, however performance of this will go down as the number of returned results increases.
这个想法ORDER BY CAST()是正确的，但是随着返回结果数量的增加，它的性能会下降。
If it's only numerical data in this column, the best practice would be to find a suitable numerical data type and change it.
如果此列中只有数字数据，最佳做法是找到合适的数字数据类型并进行更改。
If you really can't change the column and you find yourself having performance issues, I suggest having a sort order column which contains the value cast to an integer (will nulls converted to an appropriate value).
如果您确实无法更改列并且发现自己遇到了性能问题，我建议使用一个排序顺序列，其中包含转换为整数的值（将空值转换为适当的值）。
Index the sort order column and ideally, add a trigger to the CHAR column so that inserts or updates to the char value trigger an update to the integer value.
索引排序顺序列，理想情况下，向 CHAR 列添加触发器，以便插入或更新 char 值会触发对整数值的更新。
回答by Irgendwoanders
In some situations this might be a good match:
在某些情况下，这可能是一个很好的匹配：
ORDER BY LENGTH(column_name), column_name
Especially if you have a column with either all digits or a mix of letters and digits but all with the same length.
特别是如果您有一列包含全数字或字母和数字混合但长度相同的列。
