英文:
Ordering MySQL Full Text Search results they have the identical scores
问题
我正在使用MySQL全文搜索来尝试查找所有包含单词“lane”的结果,如下所示:
SELECT *
MATCH (title) AGAINST ('lane') AS score
FROM
`books`
WHERE
MATCH (title) AGAINST ('lane') AS score
ORDER
BY score DESC
这个方法可以正常工作并返回预期的结果。问题在于它们都具有相同的分数,因此它们在结果中随机出现。我想按它们第一次出现的顺序进行排序,类似这样:
Lanes
Lane Of The Day
Red Lane
House On The Lane
Search For Hidden Lane
或类似的方式,当它们都具有相同的分数时,我是否有其他排序选项?
英文:
I am using MySQL Full Text Search to try and find all results for the word lane
like this..
SELECT *
MATCH (title) AGAINST ('lane') AS score
FROM
`books`
WHERE
MATCH (title) AGAINST ('lane') AS score
ORDER
BY score DESC
This is working and returning expected results, the problem is that they all have an equal score so they appear randomly in the results. I would like to sort them by first appearance, something like this...
Red Lane
Lanes
Search For Hidden Lane
House On The Lane
Lane Of The Day
Would turn into..
Lanes
Lane Of The Day
Red Lane
House On The Lane
Search For Hidden Lane
Or something similar, do I have any other options on ordering when they all return the same score?
答案1
得分: 1
以下是代码的翻译:
SELECT *,
MATCH (title) AGAINST ('lane' IN BOOLEAN MODE) AS score
FROM
`books`
WHERE
MATCH (title) AGAINST ('lane' IN BOOLEAN MODE) > 0
ORDER
BY score DESC, POSITION('lane' IN title)
title | score |
---|---|
Lane Of The Day | 0.009391550906002522 |
Red Lane | 0.009391550906002522 |
House On The Lane | 0.009391550906002522 |
Search For Hidden Lane | 0.009391550906002522 |
英文:
For your text you can use the Position if Lane in the text, but i still wonder how you got Lanes as result
SELECT *,
MATCH (title) AGAINST ('lane' IN BOOLEAN MODE) AS score
FROM
`books`
WHERE
MATCH (title) AGAINST ('lane'IN BOOLEAN MODE) >0
ORDER
BY score DESC,POSITION('lane' IN title)
title | score |
---|---|
Lane Of The Day | 0.009391550906002522 |
Red Lane | 0.009391550906002522 |
House On The Lane | 0.009391550906002522 |
Search For Hidden Lane | 0.009391550906002522 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论