在 MySQL 中将某些值排序到顶部?
mysqlmysqli database
您需要使用 ORDER BY 子句进行排序。语法如下 −
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) -> ); Query OK, 0 rows affected (1.36 sec)
使用 insert 命令在表中插入一些记录。 查询语句如下 −
mysql> insert into SortCertainValues(Name,CountryName) values('Adam','US');
Query OK, 1 row affected (0.35 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('John','UK');
Query OK, 1 row affected (0.74 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Bob','US');
Query OK, 1 row affected (0.20 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Carol','Denmark');
Query OK, 1 row affected (0.32 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Sam','US');
Query OK, 1 row affected (0.19 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('David','France');
Query OK, 1 row affected (0.13 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Maxwell','AUS');
Query OK, 1 row affected (0.25 sec)
mysql> insert into SortCertainValues(Name,CountryName) values('Kevin','NewZealand');
Query OK, 1 row affected (0.29 sec)
使用 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)
以下查询用于将特定值排序到顶部。我们将 CountryName US 设置为顶部，即所有 CountryName 为 US 的记录 −
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)
