人们使用不同的技巧来做到这一点。我通过谷歌找到了一些结果,每个都遵循不同的技巧。看一下它们:
编辑:
我刚刚添加了每个链接的代码,以供未来的访问者参考。
MySQL中的字母数字排序
给定输入
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
MySQL中的自然排序
给定输入
表: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;