SQL Server加权全文搜索

Question

目前我有一个表格，我会按照四个字段进行搜索，分别是名字、姓氏、中间名和别名。我目前使用CONTAINSTABLE搜索行，虽然可以工作，但效果并不好。现在，我希望将名字的权重提高，中间名的权重降低。 我发现了命令ISABOUT，但如果我必须按单词而不是按列来执行它，那它似乎就没什么用（希望我理解错了）。如果按单词输入，这不是一种选择，因为我不知道用户将输入多少单词。

我在这里找到了同样的解决方案的线程，但是我无法使被接受的解决方案正常工作。也许我做错了什么，但无论如何，我无法让它工作，而且它的逻辑似乎真的很奇怪。肯定有更简单的方法。

- corymathews

Answer 1

使用联合是操纵排名的关键。对于每一列，您需要使用单独的select语句。在该语句中，添加一个标识符来显示从哪个列中提取了每行，然后将结果插入到表变量中，然后您可以通过排序标识符或根据标识符乘以某个值来操作排名。

关键是要给出修改排名的外观，而不是实际更改sql服务器的排名。

以下是使用表变量的示例：

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)的表People，每个列都添加到全文目录中，您可以使用以下查询：

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。

- Dave_H

---

这非常有帮助！真是遗憾这不是SQL Server的内置功能。 - Ryan Hoffman

Answer 2

等级在各个索引之间是无用的，你不能合并它们并期望结果有意义。每个索引的等级数字都是苹果/橙子/葡萄/西瓜/成对比较，与其他索引的内容没有相对意义。

当然，你可以尝试链接/加权/排序等级来尝试伪造有意义的结果，但最终这个结果仍然是无意义的，但根据你的具体情况，可能仍然足够提供可行的解决方案。

在我看来，最好的解决方案是将所有要搜索的数据放入单个FTS索引/列中，并使用该列的等级来排序输出...即使你必须复制字段内容以实现结果。

- Einstein

Answer 3

就在几周前，我也遇到了非常类似的问题，解决方案非常简单（尽管有些丑陋且占用空间）。创建另一列，其中包含按照此顺序组合的值：FirstName + FirstName + LastName + MiddleName。重复的FirstName列不是打字错误，它是一种技巧，可以强制FT在搜索时更高地加权FirstName的值。

- Pavel Urbančík

Answer 4

这样怎么样：

    SELECT p.* from Person p
left join ContainsTable(Person, FirstName, @SearchValue) firstnamefilter on firstnamefiler.key = p.id
left join ContainsTable(Person, MiddleName, @SearchValue) middlenamefilter on middlenamefilter.key = p.id
where (firstnamefilter.rank is not null or middlenamefilter.rank is not null)
order by firstnamefilter.rank desc, middlenamefilter.rank desc

这将为每个Person记录生成一个记录，其中第一个或中间名称（或两者）与搜索项匹配，并按照首先对所有与名字匹配的内容进行排序（按降序排列），然后是所有与中间名称匹配的内容（再次按降序排列）。

- Simon Green

Answer 5

我假设返回的数据已经与架构中的其他表连接？我会基于相关数据的列开发自己的RANK，以完整文本索引为基础。这还提供了RANK值的保证准确性。

- Coolcoder