在MySQL中将某些值排序到顶部？
您需要使用ORDERBY子句进行排序。语法如下-
SELECT *FROM yourTableName ORDER BY yourColumnName='yourValue' DESC,yourIdColumnName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table SortCertainValues -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(20), -> CountryName varchar(10), -> PRIMARY KEY(Id) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into SortCertainValues(Name,CountryName) values('Adam','US');
mysql> insert into SortCertainValues(Name,CountryName) values('John','UK');
mysql> insert into SortCertainValues(Name,CountryName) values('Bob','US');
mysql> insert into SortCertainValues(Name,CountryName) values('Carol','Denmark');
mysql> insert into SortCertainValues(Name,CountryName) values('Sam','US');
mysql> insert into SortCertainValues(Name,CountryName) values('David','France');
mysql> insert into SortCertainValues(Name,CountryName) values('Maxwell','AUS');
mysql> insert into SortCertainValues(Name,CountryName) values('Kevin','NewZealand');使用select语句显示表中的所有记录。查询如下-
mysql> select *from SortCertainValues;
以下是输出-
+----+---------+-------------+ | Id | Name | CountryName | +----+---------+-------------+ | 1 | Adam | US | | 2 | John | UK | | 3 | Bob | US | | 4 | Carol | Denmark | | 5 | Sam | US | | 6 | David | France | | 7 | Maxwell | AUS | | 9 | Kevin | NewZealand | +----+---------+-------------+ 8 rows in set (0.00 sec)
这是将某些值排序到顶部的查询。我们将CountryNameUS设置在顶部，即所有带有USCountryName的记录-
mysql> select *from SortCertainValues order by CountryName='US' DESC,Id;
以下是输出-
+----+---------+-------------+ | Id | Name | CountryName | +----+---------+-------------+ | 1 | Adam | US | | 3 | Bob | US | | 5 | Sam | US | | 2 | John | UK | | 4 | Carol | Denmark | | 6 | David | France | | 7 | Maxwell | AUS | | 9 | Kevin | NewZealand | +----+---------+-------------+ 8 rows in set (0.00 sec)