MySQL 如何使用ORDER BY LIKE

要在MySQL中使用ORDER BY LIKE，请使用CASE语句。语法如下：-

SELECT * FROM yourTableName
   ORDER BY CASE
   WHEN yourColumnName like '%yourPatternValue1%' then 1
   WHEN yourColumnName like '%yourPatternValue2%' then 2
else 3
end;

为了理解上述语法，让我们创建一个表。创建表的查询如下：-

mysql> create table OrderByLikeDemo
   ->
   ( Id int,
   Name varchar(100)
   );
Query OK, 0 rows affected (1.84 sec)

使用insert命令向表中插入一些记录。查询如下：-

mysql> insert into OrderByLikeDemo values(100,'John Smith');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByLikeDemo values(101,'Carol Taylor');
Query OK, 1 row affected (0.23 sec)

mysql> insert into OrderByLikeDemo values(102,'David Miller');
Query OK, 1 row affected (0.12 sec)

mysql> insert into OrderByLikeDemo values(103,'Mike Taylor');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByLikeDemo values(104,'Bob Miller');
Query OK, 1 row affected (0.11 sec)

mysql> insert into OrderByLikeDemo values(105,'Sam Williams');
Query OK, 1 row affected (0.16 sec)

使用select语句显示表中的所有记录。查询如下：-

mysql> select *from OrderByLikeDemo;

输出如下：-

+------+--------------+
| Id   | Name         |
+------+--------------+
| 100  | John Smith   |
| 101  | Carol Taylor |
| 102  | David Miller |
| 103  | Mike Taylor  |
| 104  | Bob Miller   |
| 105  | Sam Williams |
+------+--------------+
6 rows in set (0.00 sec)

以下是获取所有记录的ORDER BY LIKE查询：-

mysql> select *from OrderByLikeDemo
   ->
   order by case
   ->
   when Name like '%Taylor%' then 1
   ->
   when Name like '%Miller%' then 2
   ->
   else 3
   ->
   end;

输出如下：-

+------+--------------+
| Id   | Name         |
+------+--------------+
| 101  | Carol Taylor |
| 103  | Mike Taylor  |
| 102  | David Miller |
| 104  | Bob Miller   |
| 100  | John Smith   |
| 105  | Sam Williams |
+------+--------------+
6 rows in set (0.00 sec)

阅读更多：MySQL 教程