mysql - SQL 按字符串末尾的数字排序

Question

我有一个包含一些列的表，我想按“post_id”列对选择进行排序。行是这样的:'rgpost0', 'rgpost1','rcpost2', ...

如何按“post_id”列值末尾的数字降序排列选择？

此代码无效:SELECT * FROM posts_info ORDER BY post_id+0 DESC

我不想将列类型更改为数字。我只想按字符串末尾的数字排序。

Answer 1

您可以使用 reverse()两次:

SELECT * FROM posts_info 
ORDER BY reverse(reverse(post_id) + 0) + 0 DESC

对于这张表:

create table posts_info(id int, post_id varchar(100));
insert into posts_info(id, post_id) values
(1, 'bob52'),
(2, 'alice634'),
(3, 'john12'),
(4, 'mark7'),
(5, 'mary001');

结果:

| id  | post_id  |
| --- | -------- |
| 2   | alice634 |
| 1   | bob52    |
| 3   | john12   |
| 4   | mark7    |
| 5   | mary001  |

参见 demo .