MySQL中的带字母数字排序
假设您在表中有一个包含字符串值且数字在右侧的VARCHAR列。例如:
John1023
Carol9871
David9098
现在,考虑您想要按整个列中的这些右侧数字进行排序。为此,请使用ORDER BY RIGHT。
让我们首先创建一张表 –
mysql> create table DemoTable757 (
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
ClientId varchar(100)
);
Query OK, 0 rows affected (0.53 sec)
使用插入命令将一些记录插入表中 –
mysql> insert into DemoTable757(ClientId) values('John1023');
Query OK, 1 row affected (0.41 sec)
mysql> insert into DemoTable757(ClientId) values('Carol9871');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable757(ClientId) values('David9098');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable757(ClientId) values('Adam9989');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable757(ClientId) values('Bob9789');
Query OK, 1 row affected (0.20 sec)
使用select语句从表中显示所有记录 –
mysql> select *from DemoTable757;
这将产生以下输出 –
+----+-----------+
| Id | ClientId |
+----+-----------+
| 1 | John1023 |
| 2 | Carol9871 |
| 3 | David9098 |
| 4 | Adam9989 |
| 5 | Bob9789 |
+----+-----------+
5 rows in set (0.00 sec)
以下是MySQL中带有字母数字顺序的查询 –
mysql> select Id,ClientId from DemoTable757 order by right(ClientId,4);
这将产生以下输出 –
+----+-----------+
| Id | ClientId |
+----+-----------+
| 1 | John1023 |
| 3 | David9098 |
| 5 | Bob9789 |
| 2 | Carol9871 |
| 4 | Adam9989 |
+----+-----------+
5 rows in set (0.00 sec)
阅读更多:MySQL 教程