SQL 按字母顺序排序,对于小于 10 的数字不加前面的零
是否可以按以下方式在 mySQL 中按字母顺序排序:
A1
A2
A3
A5
A10
A11
A20
A23
A24
不幸的是,我收到的订单如下所示。请注意 A10 之前的任何数字如何没有组合在一起?
A1
A10
A11
A12
A2< A2
A20
A23
A24
A3< A3
A5< A5
注: 这些字母数字字符串实际上是邮政编码,因此我不能输入 A01,因为从技术上讲,这不是邮政编码前缀。我还想避免让用户输入其他数据来帮助系统对其进行正确排序,因为我的用户不太精通网络。此外,这些字母数字字符串将无法以正确的顺序输入数据库,因为它们可以随时删除和添加。
Is it possible to order alphabetically in mySQL in the following fashion:
A1
A2
A3
A5
A10
A11
A20
A23
A24
Unfortunately the order that I am getting is ordered as seen below here. Notice how any number before A10 aren't grouped together?
A1
A10
A11
A12
A2 < A2
A20
A23
A24
A3 < A3
A5 < A5
Note:
These alphanumeric strings are actually postal codes, so I cannot put A01, because that's not technically a postal code prefix. I would also like to avoid having the user to enter in other data to help the system sort it properly, because my users aren't very web savvy. Also, these alphanumeric strings will not be able to be input into the database in the correct order because they can be deleted and added at anytime.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
创建一个将 varchar 转换为数值的 UDF。然后在查询的 ORDER BY 子句中使用该函数。
基本上,您的函数将为每一行分配一个数值,例如 A 的 ASCII 值(或第一个字母是什么)* 100 + varchar 其余部分的解析数字。
Create a UDF that converts your varchar into a numeric value. Then use that function in the ORDER BY clause of your query.
Basically your function will assign a numeric value to each row, something like ASCII value of A(or whatever the first letter is) * 100 + the parsed numeric of the rest of the varchar.
如果字符串中的数字部分位于字符串中的一致位置,则可以使用表达式来选取字符串的数字部分。然后通过添加零将数字字符串强制为其整数值。
You can use an expression to pick out the numeric portion of the string, if it's in a consistent position in the string. Then coerce the numeric string to its integer value by adding zero to it.
一种解决方案是准确找出邮政编码的格式,然后将其分解为多个部分。假设它是州、邮政编码:所以您可以拥有 NC27605。然后,您可以分别保存状态和邮政编码,并将邮政编码作为计算值。然后让它们按顺序排列意味着先按州排序,然后按邮政编码排序。
这始终被认为是好的风格;数据库的重点是让所有独立的数据位都可以独立访问。
One solution is to figure out exactly what the format of a postal code is and then break it up into its parts. Let's say it was state, zip code: so you could have NC27605. Then you can save state, and zip_code separately and have the postal code be a computed value. Then getting them in order would mean sorting on state then zip_code.
This is always considered good style; the point of a database is to have all independent bits of data be independently accessible.
排序是按字母顺序排列的。
但你想把它们当作数字来排序。不幸的是 ASC 和 DESC 并没有提供太多的灵活性。
我的 MySQL 副本,New Riders,2000,作者:Paul DuBois(现在有点过时了)说
如果此功能仍然可用,并且您可以找到内置函数或强制转换将 varchar 转换为整数,是否可以
ORDER BY那个函数?还是我读得太多了?
嗯,我认为比尔·卡文(Bill Karwin)表达得最好。
The ordering is alphabetical.
But you want to order them as if they're numbers. Unfortunately ASC and DESC doesn't give much flexibility.
My copy of MySQL, New Riders, 2000, by Paul DuBois (now a little out of date) says
If this feature is still available, and you can find a built-in function or cast to convert the varchar to an integer, would it be possible to
ORDER BY
that function? Or, am I reading too much into it?Hmmm, I think Bill Karwin expresses it best.