MySQL 'Order By' - 正确排序字母数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8557172/
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
MySQL 'Order By' - sorting alphanumeric correctly
提问by Shackrock
I want to sort the following data items in the order they are presented below (numbers 1-12):
我想按照下面显示的顺序对以下数据项进行排序（数字 1-12）：
1 2 3 4 5 6 7 8 9 10 11 12
However, my query - using order by xxxxx ascsorts by the first digit above all else:
但是，我的查询-首先使用order by xxxxx asc第一个数字排序：
1 10 11 12 2 3 4 5 6 7 8 9
Any tricks to make it sort more properly?
有什么技巧可以让它更正确地排序？
Further, in the interest of full disclosure, this could be a mix of letters and numbers (although right now it is not), e.g.:
此外，为了充分披露，这可能是字母和数字的混合（尽管现在不是），例如：
A1 534G G46A 100B 100A 100JE
etc....
等等....
Thanks!
谢谢！
update: people asking for query
更新：人们要求查询
select * from table order by name asc
回答by Jomoos
People use different tricks to do this. I Googled and find out some results each follow different tricks. Have a look at them:
人们使用不同的技巧来做到这一点。我在谷歌上搜索并发现了一些结果，每个结果都遵循不同的技巧。看看他们：
- Alpha Numeric Sorting in MySQL
- Natural Sorting in MySQL
- Sorting of numeric values mixed with alphanumeric values
- mySQL natural sort
- Natural Sort in MySQL
Edit:
编辑：
I have just added the code of each link for future visitors.
我刚刚为未来的访问者添加了每个链接的代码。
Alpha Numeric Sorting in MySQL
Given input
给定输入
1A 1a 10A 9B 21C 1C 1D
Expected output
预期输出
1A 1C 1D 1a 9B 10A 21C
Query
询问
Bin Way
===================================
SELECT
tbl_column,
BIN(tbl_column) AS binray_not_needed_column
FROM db_table
ORDER BY binray_not_needed_column ASC , tbl_column ASC
-----------------------
Cast Way
===================================
SELECT
tbl_column,
CAST(tbl_column as SIGNED) AS casted_column
FROM db_table
ORDER BY casted_column ASC , tbl_column ASC
Given input
给定输入
Table: sorting_test -------------------------- ------------- | alphanumeric VARCHAR(75) | integer INT | -------------------------- ------------- | test1 | 1 | | test12 | 2 | | test13 | 3 | | test2 | 4 | | test3 | 5 | -------------------------- -------------
Expected Output
预期产出
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 4 |
| test3 | 5 |
| test12 | 2 |
| test13 | 3 |
-------------------------- -------------
Query
询问
SELECT alphanumeric, integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
Sorting of numeric values mixed with alphanumeric values
Given input
给定输入
2a, 12, 5b, 5a, 10, 11, 1, 4b
Expected Output
预期产出
1, 2a, 4b, 5a, 5b, 10, 11, 12
Query
询问
SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;
Hope this helps
希望这可以帮助
回答by antoine
I know this post is closed but I think my way could help some people. So there it is :
我知道这篇文章已经关闭，但我认为我的方法可以帮助一些人。所以它是：
My dataset is very similar but is a bit more complex. It has numbers, alphanumeric data :
我的数据集非常相似，但有点复杂。它有数字、字母数字数据：
1
2
Chair
3
0
4
5
-
Table
10
13
19
Windows
99
102
Dog
I would like to have the '-' symbol at first, then the numbers, then the text.
我想先有“-”符号，然后是数字，然后是文本。
So I go like this :
所以我是这样的：
SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum
FROM table
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name
The result should be something :
结果应该是：
-
0
1
2
3
4
5
10
13
99
102
Chair
Dog
Table
Windows
The whole idea is doing some simple check into the SELECT and sorting with the result.
整个想法是对 SELECT 进行一些简单的检查并对结果进行排序。
回答by AO_
Just do this:
只需这样做：
SELECT * FROM table ORDER BY column `name`+0 ASC
Appending the +0 will mean that:
附加 +0 意味着：
0, 10, 11, 2, 3, 4
0, 10, 11, 2, 3, 4
becomes :
变成：
0, 2, 3, 4, 10, 11
0, 2, 3, 4, 10, 11
回答by ajreal
回答by Blouarf
I had some good results with
我有一些很好的结果
SELECT alphanumeric, integer FROM sorting_test ORDER BY CAST(alphanumeric AS UNSIGNED), alphanumeric ASC
回答by Shiv Buyya
This works for type of data: Data1, Data2, Data3 ......,Data21. Means "Data" String is common in all rows.
这适用于数据类型：Data1、Data2、Data3 ......、Data21。表示“数据”字符串在所有行中都是通用的。
For ORDER BY ASC it will sort perfectly, For ORDER BY DESC not suitable.
对于 ORDER BY ASC 它将完美排序，对于 ORDER BY DESC 不适合。
SELECT * FROM table_name ORDER BY LENGTH(column_name), column_name ASC;
回答by Weston Ganger
If you need to sort an alpha-numeric column that does not have any standard format whatsoever
如果您需要对没有任何标准格式的字母数字列进行排序
SELECT * FROM table ORDER BY (name = '0') DESC, (name+0 > 0) DESC, name+0 ASC, name ASC
You can adapt this solution to include support for non-alphanumeric characters if desired using additional logic.
如果需要，您可以使用附加逻辑调整此解决方案以包含对非字母数字字符的支持。
回答by nishantagarwal
This type of question has been asked previously.
以前已经问过这种类型的问题。
The type of sorting you are talking about is called "Natural Sorting". The data on which you want to do sort is alphanumeric. It would be better to create a new column for sorting.
您所说的排序类型称为“自然排序”。您要对其进行排序的数据是字母数字的。最好创建一个新列进行排序。
For further help check natural-sort-in-mysql
如需进一步帮助，请检查 natural-sort-in-mysql
回答by user8255718
This should sort alphanumeric field like:
1/ Number only, order by 1,2,3,4,5,6,7,8,9,10,11etc...
2/ Then field with text like: 1foo, 2bar, aaa11aa, aaa22aa, b5452etc...
这应该对字母数字字段进行排序，例如：1/ 仅数字order by 1,2,3,4,5,6,7,8,9,10,11等... 2/ 然后字段与文本类似：1foo, 2bar, aaa11aa, aaa22aa, b5452等...
SELECT MyField
FROM MyTable
order by
IF( MyField REGEXP '^-?[0-9]+$' = 0,
9999999999 ,
CAST(MyField AS DECIMAL)
), MyField
The query check if the data is a number, if not put it to 9999999999 , then order first on this column, then order on data with text
查询检查数据是否为数字，如果不是，则将其放入 9999999999 ，然后在此列上先排序，然后对带文本的数据进行排序
Good luck!
祝你好运！
回答by user7646370
SELECT length(actual_project_name),actual_project_name,
SUBSTRING_INDEX(actual_project_name,'-',1) as aaaaaa,
SUBSTRING_INDEX(actual_project_name, '-', -1) as actual_project_number,
concat(SUBSTRING_INDEX(actual_project_name,'-',1),SUBSTRING_INDEX(actual_project_name, '-', -1)) as a
FROM ctts.test22
order by
SUBSTRING_INDEX(actual_project_name,'-',1) asc,cast(SUBSTRING_INDEX(actual_project_name, '-', -1) as unsigned) asc
