问题描述
我要按以下顺序显示以下数据项(编号1-12):
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
但是,我的查询-使用order by xxxxx asc
时按其他所有字母的前一位排序:
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
等...
谢谢!
更新:要求查询的人
select * from table order by name asc
推荐答案
人们使用不同的技巧来做到这一点.我用Google搜索了一下,发现每个结果都有不同的技巧.看看他们:
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
我刚刚为以后的访问者添加了每个链接的代码.
I have just added the code of each link for future visitors.
提供输入
1A 1a 10A 9B 21C 1C 1D
预期输出
1A 1C 1D 1a 9B 10A 21C
查询
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
提供输入
Table: sorting_test
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test12 | 2 |
| test13 | 3 |
| test2 | 4 |
| test3 | 5 |
-------------------------- -------------
预期产量
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 4 |
| test3 | 5 |
| test12 | 2 |
| test13 | 3 |
-------------------------- -------------
查询
SELECT alphanumeric, integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
提供输入
2a, 12, 5b, 5a, 10, 11, 1, 4b
预期产量
1, 2a, 4b, 5a, 5b, 10, 11, 12
查询
SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;
希望这会有所帮助
这篇关于MySQL'Order By'-正确对字母数字进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!