在MySQL中混合字母数字列的顺序。

I have the following values in my rows (they should be ordered like this);

我的行中有以下值(它们应该这样排序);

**CLASS_CODE**
6A
6B
6C
10A
10B
10C

Well if I do a simple ORDER BY CLASS_CODE, I will get first 10x then 6x values.

如果我用CLASS_CODE做一个简单的排序，首先得到10x，然后是6x。

So I use ORDER BY (CLASS_CODE+0), this orders them correctly so that 6x comes first, but it does not order them accordingly to chars as well.

所以我使用ORDER BY (CLASS_CODE+0)，这个命令是正确的，所以6x是第一位的，但是它并没有相应地将它们排序为chars。

What would be the correct way to order by, so I can get the correct order as shown above?

什么是正确的订购方式，以便我可以得到如上所示的正确的订单?

4 个解决方案

#1

As long as you only use a single letter at the end of the field values, then you can use the following:

只要在字段值的末尾只使用一个字母，则可以使用以下内容:

ORDER BY (class_code+0), right(class_code, 1)

#2

There is something here : http://www.carlos-roque.com/2011/04/19/sorting-characters-and-numbers-in-mysql/

这里有一些东西:http://www.carlos-roque.com/2011/04/19/sorting-character -and-numbers-in mysql/

I can't test it right now, but I believe something like that can be a nice hack :

我现在不能测试它，但我相信这样的东西可以是一个很好的技巧:

SELECT CLASS_CODE as hack ... ORDER BY (CLASS_CODE+0)ASC, hack ASC

Maybe try to turn it around.

也许试着改变一下。

If that's a fail, here is some discussion about sorting in a natural fashion : Natural Sort in MySQL

如果这是失败的，这里有一些关于以自然方式排序的讨论:MySQL中的自然排序

#3

Sorry for the late reply. But it may help someone.

对不起，回复晚了。但它可能会帮助某些人。

You have to implement Natural Sorting. To sort it using PHP, you can use natsort() function.

你必须实现自然排序。要使用PHP对其进行排序，可以使用natsort()函数。

You can achieve natural sorting through MySQL also which is simple enough. For ex:

通过MySQL可以实现自然排序，这也很简单。为例:

ORDER BY LENGTH(class_code), class_code

#4

I have had this scenario and I use the CAST function. In this case ORDER BY CAST(class_code AS DECIMAL)

我有过这个场景，我使用了CAST函数。在这种情况下，按CAST进行排序(class_code为DECIMAL)

#1