MySQL-如何按相对顺序排序? INNODB表 [英] MySQL - How to ORDER BY RELEVANCE? INNODB Table

问题描述

我在INNODB表中有大约20,000行，称为"cards"，因此不能使用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)"，我们获取了他的最新词条.根据我的数据，所有名称均指同一个确切的人，无需担心两个名为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.

推荐答案

版本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

这篇关于MySQL-如何按相对顺序排序? INNODB表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！