MySQL 'Order By' - 正确排序字母数字
我想按照下面显示的顺序(数字 1-12)对以下数据项进行排序:
<前>123456789101112但是，我的查询 - 使用 order by xxxxx asc 按第一位数字排序:
有什么技巧可以让它更正确地排序?
此外，为了充分披露，这可能是字母和数字的混合(尽管现在不是)，例如:
<前>A1534GG46A100B100A100JE等等....
谢谢！
更新:人们要求查询
select * from table order by name asc人们使用不同的技巧来做到这一点.我在谷歌上搜索并发现了一些结果，每个结果都遵循不同的技巧.看看他们:
- MySQL 中的字母数字排序
- MySQL 中的自然排序
- 混合数值的排序带有字母数字值
- mySQL 自然排序
- MySQL 中的自然排序
我刚刚为未来的访问者添加了每个链接的代码.
MySQL 中的字母数字排序
给定输入
1A 1a 10A 9B 21C 1C 1D
预期输出
1A 1C 1D 1a 9B 10A 21C
查询
Bin Way====================================选择tbl_column，BIN(tbl_column) AS binray_not_needed_column从 db_tableORDER BY binray_not_needed_column ASC , tbl_column ASC-----------------------铸造方式====================================选择tbl_column，CAST(tbl_column as SIGNED) AS casted_column从 db_tableORDER BY casted_column ASC , tbl_column ASCMySQL 中的自然排序
给定输入
表:sorting_test-------------------------- -------------|字母数字 VARCHAR(75) |整数 INT |-------------------------- -------------|测试1 |1 ||测试12 |2 ||测试13 |3 ||测试2 |4 ||测试3 |5 |-------------------------- -------------
预期产出
-------------------------- -------------|字母数字 VARCHAR(75) |整数 INT |-------------------------- -------------|测试1 |1 ||测试2 |4 ||测试3 |5 ||测试12 |2 ||测试13 |3 |-------------------------- -------------查询
SELECT 字母数字，整数FROM sort_testORDER BY LENGTH(字母数字)，字母数字混合数值的排序带有字母数字值
给定输入
2a, 12, 5b, 5a, 10, 11, 1, 4b预期产出
1, 2a, 4b, 5a, 5b, 10, 11, 12查询
SELECT 版本FROM version_sortingORDER BY CAST(版本未签名)，版本；希望能帮到你
I want to sort the following data items in the order they are presented below (numbers 1-12):
1 2 3 4 5 6 7 8 9 10 11 12
However, my query - using order by xxxxx asc sorts by the first digit above all else:
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
解决方案
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
Natural Sorting in MySQL
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
相关文章