資料結構
網路
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 檢視
廣告