MySQL-SELECT…特定列的输入(..)顺序在哪里？
您可以使用field()函数选择….WHEREidIN(..)来对任何列进行排序。语法如下-
SELECT *FROM yourTableName WHERE yourColumnName IN(‘value1’,’value2’,.......N) ORDER BY FIELD(yourColumnName,value1’,’value2’,.......N);
要了解上述语法，让我们创建一个表-
mysql> create table SelectOrderbyField -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(30), -> Age int, -> PRIMARY KEY(Id) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into SelectOrderbyField(Name,Age) values('John',23);
mysql> insert into SelectOrderbyField(Name,Age) values('Carol',25);
mysql> insert into SelectOrderbyField(Name,Age) values('Bob',21);
mysql> insert into SelectOrderbyField(Name,Age) values('Mike',28);
mysql> insert into SelectOrderbyField(Name,Age) values('Sam',26);
mysql> insert into SelectOrderbyField(Name,Age) values('David',23);使用select语句显示表中的所有记录。查询如下-
mysql> select *from SelectOrderbyField;
以下是输出-
+----+-------+------+ | Id | Name | Age | +----+-------+------+ | 1 | John | 23 | | 2 | Carol | 25 | | 3 | Bob | 21 | | 4 | Mike | 28 | | 5 | Sam | 26 | | 6 | David | 23 | +----+-------+------+ 6 rows in set (0.00 sec)
以下是按field()函数排序以选择...whereidin(..)的查询。
带ID的订单列
查询如下-
mysql> select *from SelectOrderbyField where Id IN(5,1,3,2,6,4) -> order by field(Id,5,1,3,2,6,4);
以下是输出-
+----+-------+------+ | Id | Name | Age | +----+-------+------+ | 5 | Sam | 26 | | 1 | John | 23 | | 3 | Bob | 21 | | 2 | Carol | 25 | | 6 | David | 23 | | 4 | Mike | 28 | +----+-------+------+ 6 rows in set (0.00 sec)
带名称的订单列
您可以根据“名称”列正确排序。查询如下-
mysql> select *from SelectOrderbyField where Name IN('Sam','John','Bob','Carol','David','Mike')
-> order by field(Name,'Sam','John','Bob','Carol','David','Mike');以下是输出-
+----+-------+------+ | Id | Name | Age | +----+-------+------+ | 5 | Sam | 26 | | 1 | John | 23 | | 3 | Bob | 21 | | 2 | Carol | 25 | | 6 | David | 23 | | 4 | Mike | 28 | +----+-------+------+ 6 rows in set (0.03 sec)