在 MySQL select 语句 IN 子句中按值顺序排序?
mysqlmysqli database
您可以使用 field() 函数和 ORDER BY 子句按值顺序排序。语法如下
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 -> ); Query OK, 0 rows affected (1.04 sec)
使用 insert 命令在表中插入记录。 查询语句如下
mysql> insert into SelectInDemo values(1,'Mike',23); Query OK, 1 row affected (0.21 sec) mysql> insert into SelectInDemo values(10,'Bob',21); Query OK, 1 row affected (0.16 sec) mysql> insert into SelectInDemo values(11,'Carol',30); Query OK, 1 row affected (0.25 sec) mysql> insert into SelectInDemo values(15,'Sam',24); Query OK, 1 row affected (0.15 sec) mysql> insert into SelectInDemo values(20,'John',26); Query OK, 1 row affected (0.09 sec) mysql> insert into SelectInDemo values(101,'David',27); Query OK, 1 row affected (0.25 sec) mysql> insert into SelectInDemo values(96,'Justin',23); Query OK, 1 row affected (0.27 sec)
使用 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);
The following is the 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)
