MySQL 'Order By' - 正确排序字母数字

MySQL #39;Order By#39; - sorting alphanumeric correctly(MySQL Order By - 正确排序字母数字)

问题描述

我想按照下面显示的顺序(数字 1-12)对以下数据项进行排序:

<前>123456789101112

但是，我的查询 - 使用 order by xxxxx asc 按第一位数字排序:

<前>110111223456789

有什么技巧可以让它更正确地排序?

此外，为了充分披露，这可能是字母和数字的混合(尽管现在不是)，例如:

<前>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 ASC

MySQL 中的自然排序

给定输入

表: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' - 正确排序字母数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！