MySQL - 如何按相关顺序排序？ INNODB表

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

我在INNODB表中有大约20,000行称为'cards'，所以FULLTEXT不是一个选项。

Please consider this table:

请考虑此表：

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

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

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

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

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.

请注意，我们只拉了一次'John Smith'，我们最近参加了他的录取。由于我的数据，所有名字都是针对同一个人，不需要担心两个名叫John Smith的人。想法？如果我能澄清任何事情，请告诉我。

1 个解决方案

#1

110

version 1:

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

version 2:

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

#1

110