MySQL‘Order by’-正確排序字母數字我想按以下順序對下列數據項進行排序(編號1-12)：1
2
3
4
5
6
7
8
9
10
11
12但是，我的查詢-使用order by xxxxx asc按第一個數字排序，而不是所有其他數字：1
10
11
12
2
3
4
5
6
7
8
9有什么辦法讓它更合適嗎？此外，為了充分披露，這可能是字母和數字的混合(盡管目前并非如此)，例如：A1
534G
G46A
100B
100A
100JE等等.。謝謝!更新：要求查詢的人select * from table order by name asc
3 回答
四季花海
TA貢獻1811條經驗 獲得超5個贊
給定輸入
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 versionFROM version_sortingORDER BY CAST(version AS UNSIGNED), version;
SMILET
TA貢獻1796條經驗 獲得超4個贊
SELECT * FROM table ORDER BY column `name`+0 ASC
0, 10, 11, 2, 3, 4
0, 2, 3, 4, 10, 11
添加回答
舉報
0/150
提交
取消