MySQL使用CASE语句按字段排序
要按字段排序，请使用CASE语句。让我们首先创建一个表-
mysql> create table DemoTable(StudentId varchar(100));
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('STU-980');
mysql> insert into DemoTable values('STU-1029');
mysql> insert into DemoTable values('STU-189');
mysql> insert into DemoTable values('STU-890');使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+-----------+ | StudentId | +-----------+ | STU-980 | | STU-1029 | | STU-189 | | STU-890 | +-----------+ 4 rows in set (0.00 sec)
以下是按字段查询MySQL顺序-
mysql> select *from DemoTable order by case WHEN StudentId = 'STU-890' THEN 1 WHEN StudentId = 'STU-1029' THEN 2 WHEN StudentId = 'STU-980' THEN 3 WHEN StudentId = 'STU-189' THEN 4 end;
这将产生以下输出-
+-----------+ | StudentId | +-----------+ | STU-890 | | STU-1029 | | STU-980 | | STU-189 | +-----------+ 4 rows in set (0.08 sec)