問題描述
我想按照下面顯示的順序(數字 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
這篇關于MySQL 'Order By' - 正確排序字母數字的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！