维持MySQL“ IN”查询中的顺序？
您可以借助field命令在MySQLIN查询中维护顺序。语法如下-
select *from yourTableName anyVariableName where anyVariableName.yourColumnName in(value1,value2,......N) order by field(anyVariableName.yourColumnName,value1,value2,......N);
为了实现上述语法，让我们创建一个表-
mysql> create table OrderInDemo
−> (
−> Id int,
−> Name varchar(100),
−> Age int
−> );现在让我们在表中插入一些记录。插入记录的查询如下-
mysql> insert into OrderInDemo values(90,'David',23); mysql> insert into OrderInDemo values(9,'Sam',24); mysql> insert into OrderInDemo values(10,'Carol',19); mysql> insert into OrderInDemo values(1,'John',26); mysql> insert into OrderInDemo values(3,'Johnson',25); mysql> insert into OrderInDemo values(2,'Ramit',20);
在select语句的帮助下显示所有记录。查询如下-
mysql> select *from OrderInDemo;
以下是输出-
+------+---------+------+ | Id | Name | Age | +------+---------+------+ | 90 | David | 23 | | 9 | Sam | 24 | | 10 | Carol | 19 | | 1 | John | 26 | | 3 | Johnson | 25 | | 2 | Ramit | 20 | +------+---------+------+ 6 rows in set (0.00 sec)
首先实现我们讨论的语法，以保持MySQLIN查询的顺序。查询如下-
mysql> select *from OrderInDemo OD where OD.Id in(10,1,3) −> order by field(OD.Id,10,1,3);
以下是显示按查询提供的顺序排序的结果的输出-
+------+---------+------+ | Id | Name | Age | +------+---------+------+ | 10 | Carol | 19 | | 1 | John | 26 | | 3 | Johnson | 25 | +------+---------+------+ 3 rows in set (0.00 sec)