排序数字&PHP MySQL中的字母数字相应 [英] Sort Numeric & Alphanumeric Accordingly in PHP MySQL

问题描述

我使用的查询类似于;

1
2
3A
3
4A
4

但我想得到它

1
2
3
3A
4
4A

我使用的是

SELECT * FROM `sections` ORDER BY CAST(`act` as DECIMAL) ASC

我真正想要的是第一个数字，然后是字母数字

What I exactly want is First Number then Alphanumeric

3 then 3A

推荐答案

考虑到在 acts 列中总是先是整数，然后是字符.通常这称为自然排序.

Considering that you'll always have integer first and then characters in your acts column. Normally this is called Natural Sorting.

问题理解:

• 当我们处理单个数字的变体，即 1, 1A, 1B, 1AB.. 时，正常排序非常有效.
• 由于字符串类型，当我们对以相同数字开头的数字(如 1、10、100...)进行排序时，它会失败.

• Normal sorting works perfectly when we're dealing with variations of a single number i.e. 1, 1A, 1B, 1AB...
• Due to string type, it fails when we sort between numbers which starts with same digit like 1, 10, 100....

我们可以按以下方式排序.

We can sort in following manner.

• 首先根据整数值对它们进行排序.
• 然后将它们正常排序，默认情况下由 mysql 处理.

SELECT * FROM sections
ORDER BY CAST(acts AS UNSIGNED), acts

检查相应的fiddle.它适用于 MySQL 5.5, 5.6, 5.7 &8.0.

Check corresponding fiddle. It is working for MySQL 5.5, 5.6, 5.7 & 8.0.

这篇关于排序数字&PHP MySQL中的字母数字相应的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！