MySQL查询对列的部分值（如“ John_120”中的数字）进行排序
为此，您可以将SUBSTRING_INDEX()与ORDERBY一起使用。让我们首先创建一个表-
mysql> create table DemoTable1502 -> ( -> StudentId varchar(40) -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1502 values('John_120');
mysql> insert into DemoTable1502 values('John_201');
mysql> insert into DemoTable1502 values('Mike_178');
mysql> insert into DemoTable1502 values('Bob_198');使用select语句显示表中的所有记录-
mysql> select * from DemoTable1502;
这将产生以下输出-
+-----------+ | StudentId | +-----------+ | John_120 | | John_201 | | Mike_178 | | Bob_198 | +-----------+ 4 rows in set (0.00 sec)
以下是对列部分值进行排序的查询-
mysql> select substring_index(StudentId,'_',1) as LeftPart, -> substring_index(StudentId,'_',-1) as RightPart -> from DemoTable1502 -> order by RightPart;
这将产生以下输出-
+----------+-----------+ | LeftPart | RightPart | +----------+-----------+ | John | 120 | | Mike | 178 | | Bob | 198 | | John | 201 | +----------+-----------+ 4 rows in set (0.00 sec)