MySQL 如何按相关性排序

要按相关性排序，请使用case语句。为了理解这个概念，让我们创建一个表。创建表的查询如下所示−

mysql> create table OrderByRelevance
   -> (
   -> UserId int,
   -> UserName varchar(200)
   -> );
Query OK, 0 rows affected (0.51 sec)

现在，您可以使用insert命令在表中插入一些记录。查询如下所示−

mysql> insert into OrderByRelevance values(101,'Carol Smith');
Query OK, 1 row affected (0.18 sec)

mysql> insert into OrderByRelevance values(102,'Carol Adams');
Query OK, 1 row affected (0.17 sec)

mysql> insert into OrderByRelevance values(103,'Carolnathan Todd');
Query OK, 1 row affected (0.33 sec)

mysql> insert into OrderByRelevance values(104,'John Smith');
Query OK, 1 row affected (0.22 sec)

mysql> insert into OrderByRelevance values(105,'Sam Carol Bond');
Query OK, 1 row affected (0.12 sec)

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

mysql> select *from OrderByRelevance;

阅读更多：MySQL 教程

输出

+--------+------------------+
| UserId | UserName         |
+--------+------------------+
| 101    | Carol Smith      |
| 102    | Carol Adams      |
| 103    | Carolnathan Todd |
| 104    | John Smith       |
| 105    | Sam Carol Bond   |
+--------+------------------+
5 rows in set (0.00 sec)

以下是按相关性排序的查询。查询如下所示−

mysql> select max(UserId)as Id,UserName from OrderByRelevance
   -> where UserName like '%Carol%' group by UserName
   -> order by case when UserName like 'Carol%' THEN 0
   -> WHEN UserName like '% %Carol% %' THEN 1
   -> WHEN UserName like '%Carol' THEN 2
   -> else 3
   -> end,UserName;

输出

+------+------------------+
|   Id | UserName         |
+------+------------------+
| 102  | Carol Adams      |
| 101  | Carol Smith      |
| 103  | Carolnathan Todd |
| 105  | Sam Carol Bond   |
+------+------------------+
4 rows in set (0.00 sec)