如何在MySQL中按特定顺序排列数据？
使用ORDERBYIF()可以按特定顺序排列数据。以下是语法-
select *from yourTableName ORDER BY IF(yourColumnName=yourValue1 OR yourColumnName=yourValue2 OR yourColumnName=yourValue3,yourColumnName, ~yourColumnName) ASC;
让我们首先创建一个表-
mysql> create table arrangeDataInSpecificOrder -> ( -> StudentId int, -> StudentName varchar(20) -> );
以下是使用insert命令在表中插入一些记录的查询-
mysql> insert into arrangeDataInSpecificOrder values(10,'Larry'); mysql> insert into arrangeDataInSpecificOrder values(15,'Mike'); mysql> insert into arrangeDataInSpecificOrder values(100,'Sam'); mysql> insert into arrangeDataInSpecificOrder values(70,'Carol'); mysql> insert into arrangeDataInSpecificOrder values(90,'Bob'); mysql> insert into arrangeDataInSpecificOrder values(300,'David');
以下是使用select语句显示表中所有记录的查询-
mysql> select * from arrangeDataInSpecificOrder;
这将产生以下输出-
+-----------+-------------+ | StudentId | StudentName | +-----------+-------------+ | 10 | Larry | | 15 | Mike | | 100 | Sam | | 70 | Carol | | 90 | Bob | | 300 | David | +-----------+-------------+ 6 rows in set (0.00 sec)
这是按特定顺序排列数据的查询-
mysql> select * from arrangeDataInSpecificOrder ORDER BY IF(StudentId=300 OR StudentId=100 OR StudentId=10,StudentId, ~StudentId) ASC;
这将产生以下输出-
+-----------+-------------+ | StudentId | StudentName | +-----------+-------------+ | 10 | Larry | | 100 | Sam | | 300 | David | | 90 | Bob | | 70 | Carol | | 15 | Mike | +-----------+-------------+ 6 rows in set (0.00 sec)