SQL ORDER 字符数字
我有一列存储为字符的数字。 当我对此列执行 ORDER BY 时，我得到以下结果：
100
131
200
21
30
31000
等等。
我如何对这些字符进行数字排序？ 我是否需要转换某些内容，或者是否已经有用于此目的的 SQL 命令或函数？
谢谢。
I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following:
100
131
200
21
30
31000
etc.
How can I order these chars numerically? Do I need to convert something or is there already an SQL command or function for this?
Thank You.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
尝试这个：
Try this:
这对我有用：
This Worked for me:
在某些情况下，这可能是一个很好的匹配：
特别是如果您有一列全部是数字或字母和数字混合但长度相同的情况。
In some situations this might be a good match:
Especially if you have a column with either all digits or a mix of letters and digits but all with the same length.
这是以“自然排序”对数字字符串进行排序的问题（如果您在 Google 上查找“自然排序”，您会发现大量内容）。 本质上，将字符串转换为 int 并对结果值进行排序应该可以修复它。
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.
原因是，对于 char 数据类型，您将按字符串对行进行排序。
ORDER BY CAST()的想法是正确的，但是随着返回结果数量的增加，其性能会下降。如果此列中只有数值数据，最佳实践是找到合适的数值数据类型并更改它。
如果您确实无法更改列并且发现自己遇到性能问题，我建议使用一个排序顺序列，其中包含转换为整数的值（将空值转换为适当的值）。
对排序顺序列建立索引，理想情况下，向 CHAR 列添加触发器，以便对 char 值的插入或更新触发对整数值的更新。
The reason for this is that with a char data type, you are sorting the rows as a string.
The idea to
ORDER BY CAST()is correct, however performance of this will go down as the number of returned results increases.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.