SQL Server加权全文搜索

目前我有一个表,我搜索4个字段,FirstName,LastName,MiddleName和AKA.我目前有一个CONTAINSTABLE搜索行,它的工作原理.不太好但是有效.现在我想让名字加权更高,中间名更低.

我找到了命令ISABOUT,但是如果我不得不通过单词而不是列(希望我明白这个错误),这似乎毫无价值.这不是一个选项,因为我不知道用户将输入多少单词.

我发现这里的线程谈到了同样的解决方案但是我无法让公认的解决方案起作用.也许我做错了什么,但无论我不能让它工作,它的逻辑似乎真的......奇怪.必须有一个更简单的方法.

操纵排名的关键是使用联合.对于每列,您使用单独的select语句.在该语句中,添加一个标识符,该标识符显示每行被拉出的列.将结果插入表变量,然后您可以通过对标识符进行排序或将排名乘以基于标识符的某个值来操纵排名.

关键是要给出修改排名的外观,而不是实际更改sql server的排名.

使用表变量的示例:

DECLARE @Results TABLE (PersonId Int, Rank Int, Source Int)

对于具有列的人员PersonId Int PK Identity, FirstName VarChar(100), MiddleName VarChar(100), LastName VarChar(100), AlsoKnown VarChar(100),每列添加到完整文本目录,您可以使用查询:

INSERT INTO @Results (PersonId, Rank, Source)

SELECT PersonId, Rank, 1
FROM ContainsTable(People, FirstName, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId

UNION
SELECT PersonId, Rank, 2
FROM ContainsTable(People, MiddleName, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId

UNION
SELECT PersonId, Rank, 3
FROM ContainsTable(People, LastName, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId

UNION
SELECT PersonId, Rank, 4
FROM ContainsTable(People, AlsoKnown, @SearchValue) CT INNER JOIN People P ON CT.Key = P.PersonId

/*
Now that the results from above are in the @Results table, you can manipulate the
rankings in one of several ways, the simplest is to pull the results ordered first by Source then by Rank.  Of course you would probably join to the People table to pull the name fields.
*/

SELECT PersonId
FROM @Results
ORDER BY Source, Rank DESC

/*
A more complex manipulation would use a statement to multiply the ranking by a value above 1 (to increase rank) or less than 1 (to lower rank), then return results based on the new rank.  This provides more fine tuning, since I could make first name 10% higher and middle name 15% lower and leave last name and also known the original value.
*/

SELECT PersonId, CASE Source WHEN 1 THEN Rank * 1.1 WHEN 2 THEN Rank * .9 ELSE Rank END AS NewRank FROM @Results
ORDER BY NewRank DESC

一个缺点是你会注意到我没有使用UNION ALL,所以如果一个单词出现在多个列中,则排名不会反映出来.如果这是一个问题,您可以使用UNION ALL然后删除重复的人ID,方法是将全部或部分重复记录的等级添加到具有相同人员ID的另一记录的等级.