SQL Server加权全文搜索 [英] SQL Server Weighted Full Text Search

问题描述

目前，我有一个表格，可以搜索4个字段，名字，姓氏，中间名和AKA.我目前有 CONTAINSTABLE 搜索行，它可以正常工作.不好，但是行得通.现在我要使名"的权重较高，而使中间名"的权重较低.

Currently I have a table that I search upon 4 fields, FirstName, LastName, MiddleName, And AKA's. I currently have a CONTAINSTABLE search for the rows and it works. Not well but it works. Now I want to make the First Name weighted higher and middle name lower.

我找到了命令 ISABOUT ，但是如果我必须按单词而不是按列来执行它，那似乎就毫无用处了(希望我理解了这个错误).如果按单词输入，这不是一个选择，因为我不知道用户将输入多少个单词.

I found the command ISABOUT but that seems pretty worthless if I have to do it by word not column (hopefully I understood this wrong). This is not an option if its by word because I do not know how many words the user will enter.

我找到了线程这里讨论了相同的解决方案，但是我无法获得公认的解决方案.也许我做错了什么，但是无论如何我都做不到，它的逻辑似乎真的很奇怪.必须有一种更简单的方法.

I found the thread here that talks about this same solution however I was unable to get the accepted solution to work. Maybe I have done something wrong but regardless I cannot get it to work, and its logic seems really... odd. There has to be an easier way.

推荐答案

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

The key to manipulating the rankings is to use a union. For each column you use a separate select statement. In that statement, add an identifier that shows from which column each row was pulled then. Insert the results into a table variable, then you can manipulate the ranking by sorting on the identifier or multiplying the rank by some value based on the identifier.

关键是使外观看起来像在修改排名，而不是实际更改sql server的排名.

The key is to give the appearance of modifying the ranking, not to actually change sql server's ranking.

使用表变量的示例:

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

对于具有列 PersonId Int PK标识，FirstName VarChar(100)，MiddleName VarChar(100)，LastName VarChar(100)，AlsoKnown VarChar(100)的表列的人，每列均已添加到完整列中文本目录，您可以使用查询:

For table People with Columns PersonId Int PK Identity, FirstName VarChar(100), MiddleName VarChar(100), LastName VarChar(100), AlsoKnown VarChar(100) with each column added to a full text catalog, you could use the query:

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.

The one downside is you'll notice I didn't use UNION ALL, so if a word appears in more than one column, the rank won't reflect that. If that's an issue you could use UNION ALL and then remove duplicate person id's by adding all or part of the duplicate record's rank to the rank of another record with the same person id.

这篇关于SQL Server加权全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！