从以数字分隔的字符串记录中按数字顺序排序，例如CSE 15，CSE 11等？
让我们首先创建一个表-
create table DemoTable1969 ( BranchCode varchar(20) );
使用插入命令在表中插入一些记录-
insert into DemoTable1969 values('CSE 101');
insert into DemoTable1969 values('CSE 11');
insert into DemoTable1969 values('CSE 15');
insert into DemoTable1969 values('CSE 6');
insert into DemoTable1969 values('CSE 201');
insert into DemoTable1969 values('CSE 110');使用select语句显示表中的所有记录-
select * from DemoTable1969;
这将产生以下输出-
+------------+ | BranchCode | +------------+ | CSE 101 | | CSE 11 | | CSE 15 | | CSE 6 | | CSE 201 | | CSE 110 | +------------+ 6 rows in set (0.00 sec)
这是按数字顺序从以数字分隔的字符串记录中排序的查询-
select * from DemoTable1969
order by CAST(SUBSTRING(BranchCode,LOCATE(' ',BranchCode)+1) AS SIGNED) DESC;这将产生以下输出-
+------------+ | BranchCode | +------------+ | CSE 201 | | CSE 110 | | CSE 101 | | CSE 15 | | CSE 11 | | CSE 6 | +------------+ 6 rows in set (0.00 sec)