数据结构
网络
RDBMS
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C 编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理
化学
生物
数学
英语
经济
心理学
社会研究
服装研究
法律学习按 MySQL 选择语句 IN 子句中的值排序？
可以使用 ORDER BY 子句中的 field() 函数按值顺序排序。语法如下
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);
以下是输出
+-----------+-------------+------------+ | 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)
更新于：30-Jul-2019
119 查看
广告