如何按IN子句的顺序选择MySQL行?
您需要使用FIND_IN_SET()函数按IN子句的顺序选择MySQL行。语法如下-
SELECT yourVariableName.* FROM yourTableName yourVariableName WHERE yourVariableName.yourColumnName IN(value1,value2,...N) ORDER BY FIND_IN_SET( yourVariableName.yourColumnName,'value1,value2,...N');
为了理解上述语法,让我们创建一个表。创建表的查询如下-
mysql> create table InDemo -> ( -> CodeId int, -> Name varchar(20) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into InDemo values(1,'John'); mysql> insert into InDemo values(2,'Carol'); mysql> insert into InDemo values(3,'Sam'); mysql> insert into InDemo values(4,'Bob');
现在,您可以使用select语句显示表中的所有记录。查询如下-
mysql> select *from InDemo;
以下是输出-
+--------+-------+ | CodeId | Name | +--------+-------+ | 1 | John | | 2 | Carol | | 3 | Sam | | 4 | Bob | +--------+-------+ 4 rows in set (0.00 sec)
这是按IN子句的顺序选择MySQL行的查询-
mysql> select tbl.* -> from InDemo tbl -> where tbl.CodeId in(1,3,2,4) -> ORDER BY FIND_IN_SET( tbl.CodeId,'1,3,2,4');
以下是输出-
+--------+-------+ | CodeId | Name | +--------+-------+ | 1 | John | | 3 | Sam | | 2 | Carol | | 4 | Bob | +--------+-------+ 4 rows in set (0.00 sec)