MySQL 如何使用ORDER BY对最后2个字符的字符串排序

可以使用ORDER BY RIGHT()按最后2个字符的字符串排序。

语法如下

select yourColumnName from yourTableName ORDER BY RIGHT(yourColumnName , 2);

为了理解上述语法，让我们创建一个表。创建表的查询语句如下

mysql> create table OrderByLast2CharactersDemo
     -> (
     -> CustomerId varchar(20),
     -> CustomerName varchar(20)
     -> );
 Query OK, 0 rows affected (0.58 sec)

使用插入命令在表中插入一些记录。查询语句如下 −

mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('John-98','John');
 Query OK, 1 row affected (0.20 sec)
 mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('Carol-91','Carol');
 Query OK, 1 row affected (0.21 sec)
 mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('Bob-99','Bob');
 Query OK, 1 row affected (0.22 sec)
 mysql> insert into OrderByLast2CharactersDemo(CustomerId,CustomerName) values('David-67','David');
 Query OK, 1 row affected (0.15 sec)

使用SELECT语句从表中显示所有记录。查询语句如下 −

mysql> select *from OrderByLast2CharactersDemo;

输出如下：

+------------+--------------+
| CustomerId | CustomerName |
+------------+--------------+
| John-98    | John         |
| Carol-91   | Carol        |
| Bob-99     | Bob          |
| David-67   | David        |
+------------+--------------+
4 rows in set (0.00 sec)

下面是按最后2个字符的字符串排序的查询语句。

情况1 ：结果按升序排列。

查询语句如下 −

mysql> select CustomerId from OrderByLast2CharactersDemo ORDER BY RIGHT(CustomerId , 2);

输出如下：

+------------+
| CustomerId |
+------------+
| David-67   |
| Carol-91   |
| John-98    |
| Bob-99     |
+------------+
4 rows in set (0.01 sec)

情况2 ：结果按降序排列。

查询语句如下 −

mysql> select CustomerId from OrderByLast2CharactersDemo ORDER BY RIGHT(CustomerId , 2) DESC;

输出如下：

+------------+
| CustomerId |
+------------+
| Bob-99     |
| John-98    |
| Carol-91   |
| David-67   |
+------------+
4 rows in set (0.00 sec)

阅读更多：MySQL 教程