MySQL按相关性排序 [英] MySQL order by relevance

问题描述

我有一个搜索表单，用于搜索站点内容表以拉回适当的结果.

I have a search form which searches a site content table to pull back appropriate results.

我想搜索标题和内容字段，并按相关性顺序拉回结果.标题具有最高优先级.

I want to search the title and content fields and pull back results in order of relevance. Giving highest priority to the title.

假设我们的表(tblContent)为

Say we had a table (tblContent) of

intID | strTitle    | txtContent

1     | Smith John  | Lorem Ipsum
2     | Lorem Ipsum | Lorem John Smith Ipsum
3     | John Smith  | Lorem Ipsum Lorem Ipsum
4     | Lorem Ipsum | Lorem Ipsum Lorem Ipsum
5     | Lorem Ipsum | Lorem Ipsum Smith John

您正在搜索约翰·史密斯"，结果应按3、2、1、5、5的顺序返回

And you were searching for "John Smith" the results should come back in the order of 3,2,1,5

这怎么可能?

推荐答案

我设法对此颇为关注:

SELECT *, 
( (1.3 * (MATCH(strTitle) AGAINST ('+john+smith' IN BOOLEAN MODE))) + (0.6 * (MATCH(txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE)))) AS relevance 
FROM content 
WHERE (MATCH(strTitle,txtContent) AGAINST ('+john+smith' IN BOOLEAN MODE) ) 
ORDER BY relevance DESC

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