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

发布于 2024-08-08 09:40:54 字数 770 浏览 4 评论 0原文

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

请考虑这个表：

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”，我希望结果集的顺序为：

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

请注意，我们只提取了“John Smith”一次，我们获取了他最近的条目。根据我的数据，所有名字都是同一个人，无需担心两个名为约翰·史密斯的不同人。有想法吗？如果我能澄清任何事情，请告诉我。

原文

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

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:

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

南街女流氓 2024-08-15 09:40:54

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

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

version 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

回复收藏 0 原文

~没有更多了~