在MySQL select语句IN子句中按值顺序排序？
您可以将field()函数与ORDERBY子句一起使用，以按值顺序排序。语法如下
SELECT *FROM yourTableName WHERE yourColumnName IN(Value1,Value2,Value3,.......N); ORDER BY FIELD(yourColumnName ,Value1,Value2,Value3,.......N);
为了理解上述语法，让我们创建一个表。创建表的查询如下
mysql> create table SelectInDemo -> ( -> StudentId int, -> StudentName varchar(100), -> StudentAge int -> );
使用insert命令在表中插入记录。查询如下
mysql> insert into SelectInDemo values(1,'Mike',23); mysql> insert into SelectInDemo values(10,'Bob',21); mysql> insert into SelectInDemo values(11,'Carol',30); mysql> insert into SelectInDemo values(15,'Sam',24); mysql> insert into SelectInDemo values(20,'John',26); mysql> insert into SelectInDemo values(101,'David',27); mysql> insert into SelectInDemo values(96,'Justin',23);
使用select语句显示表中的所有记录。查询如下
mysql> select *from SelectInDemo;
以下是输出
+-----------+-------------+------------+ | StudentId | StudentName | StudentAge | +-----------+-------------+------------+ | 1 | Mike | 23 | | 10 | Bob | 21 | | 11 | Carol | 30 | | 15 | Sam | 24 | | 20 | John | 26 | | 101 | David | 27 | | 96 | Justin | 23 | +-----------+-------------+------------+ 7 rows in set (0.00 sec)
这是在MySQL中将IN与SELECT语句一起使用的查询
mysql> select *from SelectInDemo -> where StudentId IN(1,96,101,10,15,11,20) -> order by field(StudentId,1,96,101,10,15,11,20);
以下是输出
+-----------+-------------+------------+ | StudentId | StudentName | StudentAge | +-----------+-------------+------------+ | 1 | Mike | 23 | | 96 | Justin | 23 | | 101 | David | 27 | | 10 | Bob | 21 | | 15 | Sam | 24 | | 11 | Carol | 30 | | 20 | John | 26 | +-----------+-------------+------------+ 7 rows in set (0.00 sec)
让我们看看另一个订单。
对于二阶查询如下。
mysql> select *from SelectInDemo -> where StudentId IN(1,10,11,15,20,101,96) -> order by field(StudentId,1,10,11,15,20,101,96);
以下是outpu
+-----------+-------------+------------+ | StudentId | StudentName | StudentAge | +-----------+-------------+------------+ | 1 | Mike | 23 | | 10 | Bob | 21 | | 11 | Carol | 30 | | 15 | Sam | 24 | | 20 | John | 26 | | 101 | David | 27 | | 96 | Justin | 23 | +-----------+-------------+------------+ 7 rows in set (0.00 sec)