如何在 MySQL 中按最后 2 个字符串进行排序?
mysqlmysqli database
您可以使用 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)
使用 insert 命令在表中插入一些记录。 查询语句如下 −
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)
