MySQL SQL 顺序字符串作为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11808573/
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 string as number
提问by Jamol
I have numbers saved as VARCHARto a MySQL database. I can not make them INTdue to some other depending circumstances.
我将数字保存为VARCHARMySQL 数据库。INT由于其他一些情况，我无法制作它们。
It is taking them as character not as number while sorting.
它在排序时将它们作为字符而不是数字。
In database I have
在数据库中我有
1 2 3 4 5 6 7 8 9 10...
On my page it shows ordered list like this:
在我的页面上，它显示了这样的有序列表：
1 10 2 3 4 5 6 7 8 9
How can I make it appear ordered by numbers ascending?
如何让它按数字升序排列？
回答by juergen d
If possible you should change the data type of the column to a number if you only store numbers anyway.
如果可能的话，如果您只存储数字，则应该将列的数据类型更改为数字。
If you can't do that then cast your column value to an integerexplicitlywith
如果您不能这样做，则将您的列值integer显式转换为
select col from yourtable
order by cast(col as unsigned)
or implicitlyfor instance with a mathematical operation which forces a conversion to number
或隐含地例如使用强制转换为数字的数学运算
select col from yourtable
order by col + 0
BTW MySQL converts strings from left to right. Examples:
BTW MySQL 从左到右转换字符串。例子：
string value | integer value after conversion
--------------+--------------------------------
'1' | 1
'ABC' | 0 /* the string does not contain a number, so the result is 0 */
'123miles' | 123
'3' | 0 /* the left side of the string does not start with a number */
回答by Velter
Another way, without using a single cast.
另一种方式，不使用单个演员。
(For people who use JPA 2.0, where no casting is allowed)
（对于使用 JPA 2.0 的人，不允许强制转换）
select col from yourtable
order by length(col),col
EDIT: only works for positive integers
编辑：仅适用于正整数
回答by Losbear
The column I'm sorting with has any combination of alpha and numeric, so I used the suggestions in this post as a starting point and came up with this.
我正在排序的列有字母和数字的任意组合，所以我使用这篇文章中的建议作为起点并提出了这个。
DECLARE @tmp TABLE (ID VARCHAR(50));
INSERT INTO @tmp VALUES ('XYZ300');
INSERT INTO @tmp VALUES ('XYZ1002');
INSERT INTO @tmp VALUES ('106');
INSERT INTO @tmp VALUES ('206');
INSERT INTO @tmp VALUES ('1002');
INSERT INTO @tmp VALUES ('J206');
INSERT INTO @tmp VALUES ('J1002');
SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum
FROM @tmp
ORDER BY IsNum, LEN(ID), ID;
Results
结果
ID
------------------------
106
206
1002
J206
J1002
XYZ300
XYZ1002
Hope this helps
希望这可以帮助
回答by Parth Solanki
Another and simple way
另一种简单的方法
ORDER BY ABS(column_name)
ORDER BY ABS(column_name)
回答by Patrick De Guzman
This works for me.
这对我有用。
select * from tablename
order by cast(columnname as int) asc
回答by shukshin.ivan
Another way to convert.
另一种转换方式。
If you have string field, you can transform it or its numerical part the following way: add leading zeros to make all integer strings having equal length.
如果您有字符串字段，您可以通过以下方式转换它或其数字部分：添加前导零以使所有整数字符串具有相等的长度。
ORDER BY CONCAT( REPEAT( "0", 18 - LENGTH( stringfield ) ) , stringfield )
or order by part of a field something like 'tensymbols13', 'tensymbols1222' etc.
或按字段的一部分排序，例如“tensymbols13”、“tensymbols1222”等。
ORDER BY CONCAT( REPEAT( "0", 18 - LENGTH( LEFT( stringfield , 10 ) ) ) , LEFT( stringfield , 10 ) )
回答by Amir
This will handle negative numbers, fractions, string, everything:
这将处理负数，分数，字符串，一切：
ORDER BY ISNUMERIC(col) DESC, Try_Parse(col AS decimal(10,2)), col;
回答by user2400083
I was looking also a sorting fields that has letter prefix. Here is what i found out the solution. This might help who is looking for the same solution.
我也在寻找一个带有字母前缀的排序字段。这是我发现的解决方案。这可能对正在寻找相同解决方案的人有所帮助。
Field Values:
字段值：
FL01,FL02,FL03,FL04,FL05,...FL100,...FL123456789
select SUBSTRING(field,3,9) as field from table order by SUBSTRING(field,3,10)*1 desc
SUBSTRING(field,3,9)i put 9 because 9 is way enough for me to hold max 9 digits integer values.
SUBSTRING(field,3,9)我放 9 是因为 9 足以让我保存最多 9 位整数值。
So the result will be 123456789 123456788 123456787 ... 100 99 ... 2 1
所以结果将是 123456789 123456788 123456787 ... 100 99 ... 2 1
回答by Samir Lakhani
It might help who is looking for the same solution.
这可能对正在寻找相同解决方案的人有所帮助。
select * from tablename ORDER BY ABS(column_name)
回答by Ndianabasi
If you are using AdonisJS and have mixed IDs such as ABC-202, ABC-201..., you can combine raw queries with Query Builder and implement the solution above (https://stackoverflow.com/a/25061144/4040835) as follows:
如果您使用的是 AdonisJS 并且具有混合 ID，例如 ABC-202、ABC-201...，您可以将原始查询与 Query Builder 结合并实现上述解决方案 ( https://stackoverflow.com/a/25061144/4040835)如下：
const sortField =
'membership_id'
const sortDirection =
'asc'
const subquery = UserProfile.query()
.select(
'user_profiles.id',
'user_profiles.user_id',
'user_profiles.membership_id',
'user_profiles.first_name',
'user_profiles.middle_name',
'user_profiles.last_name',
'user_profiles.mobile_number',
'countries.citizenship',
'states.name as state_of_origin',
'user_profiles.gender',
'user_profiles.created_at',
'user_profiles.updated_at'
)
.leftJoin(
'users',
'user_profiles.user_id',
'users.id'
)
.leftJoin(
'countries',
'user_profiles.nationality',
'countries.id'
)
.leftJoin(
'states',
'user_profiles.state_of_origin',
'states.id'
)
.orderByRaw(
`SUBSTRING(:sortField:,3,15)*1 ${sortDirection}`,
{
sortField: sortField,
}
)
.paginate(
page,
per_page
)
NOTES:In this line: SUBSTRING(:sortField:,3,15)*1 ${sortDirection},
注：在这一行：SUBSTRING(:sortField:,3,15)*1 ${sortDirection}，
- '3'stands for the index number of the last non-numerical character before the digits. If your mixed ID is "ABC-123," your index number will be 4.
- '15'is used to catch as any number of digits as possible after the hyphen.
- '1'performs a mathematical operation on the substring which effectively casts the substring to a number.
- '3'代表数字前最后一个非数字字符的索引号。如果您的混合 ID 是“ABC-123”，则您的索引号将为 4。
- '15'用于在连字符后捕获尽可能多的数字。
- '1'对子字符串执行数学运算，有效地将子字符串转换为数字。
Ref 1: You can read more about parameter bindings in raw queries here: https://knexjs.org/#Raw-BindingsRef 2: Adonis Raw Queries: https://adonisjs.com/docs/4.1/query-builder#_raw_queries
参考 1：您可以在此处阅读有关原始查询中参数绑定的更多信息：https://knexjs.org/#Raw-Bindings 参考 2：Adonis 原始查询：https: //adonisjs.com/docs/4.1/query-builder# _raw_queries
