MySQL - 如何按相关性排序?INNODB 表
我在一个名为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
相关文章