MySQL - 如何按相关性排序?INNODB 表

2021-12-20 00:00:00 search mysql sql-order-by innodb relevance

我在一个名为cards"的 INNODB 表中有大约 20,000 行，所以 FULLTEXT 不是一个选项.

I've got about 20,000 rows in an INNODB table called 'cards', so FULLTEXT is not an option.

请考虑这张表:

id     |     name     |     description
----------------------------------------------------------
1        John Smith       Just some dude
2        Ted Johnson      Another dude
3        Johnathan Todd   This guy too
4        Susan Smith      Her too
5        Sam John Bond    And him
6        John Smith       Same guy as num 1, another record
7        John Adams       Last guy, promise

因此，假设用户搜索John"，我希望结果集的顺序为:

So, say the user searches for 'John', I want the result set to be in the order of:

7        John Adams
6        John Smith
3        Johnathan Todd
5        Sam John Bond
2        Ted Johnson

请注意，我们只提取了一次John Smith"，我们选取​​了他最近的条目.由于我的数据，所有的名字都是同一个人的，不用担心有两个不同的人叫约翰史密斯.想法?如果我能澄清任何事情，请告诉我.

Please note that we've only pulled 'John Smith' once, we took his most recent entry. Due to my data, all names are for the same exact person, no need to worry about 2 different guys named John Smith. Ideas? Let me know if I can clarify anything.

推荐答案

version 1:

SELECT max(id) id, name
  FROM cards
 WHERE name like '%John%'
 GROUP BY name
 ORDER BY CASE WHEN name like 'John %' THEN 0
               WHEN name like 'John%' THEN 1
               WHEN name like '% John%' THEN 2
               ELSE 3
          END, name

版本 2:

SELECT max(id) id, name
  FROM cards
 WHERE name like '%John%'
 GROUP BY name
 ORDER BY CASE WHEN name like 'John%' THEN 0
               WHEN name like '% %John% %' THEN 1
               WHEN name like '%John' THEN 2
               ELSE 3
          END, name

相关文章