在SQL Server 2008中跨多个表,列使用全文搜索

我需要使用全文搜索从数据库中的两个表中搜索多个列.有问题的两个表都有相关的列全文索引.

我选择全文搜索的原因:1.能够轻松搜索重音词(cafè)2.能够按照词语接近等排名.3."你的意思是XXX？" 功能

这是一个虚拟表结构,以说明挑战:

Table Book
BookID
Name (Full-text indexed)
Notes (Full-text indexed)

Table Shelf
ShelfID
BookID

Table ShelfAuthor
AuthorID
ShelfID

Table Author
AuthorID
Name (Full-text indexed)

我需要搜索书名,书注和作者姓名.

我知道有两种方法可以做到这一点:

使用全文索引视图:这可能是我首选的方法,但我不能这样做,因为要查看全文索引,它需要是模式绑定,没有任何外连接,具有唯一索引.我需要获取我的数据的视图不满足这些约束(它包含我需要从中获取数据的许多其他连接表).

在存储过程中使用连接:这种方法的问题是我需要按排名排序结果.如果我在表中进行多个连接,则默认情况下SQL Server不会跨多个字段进行搜索.我可以在两个链接表上组合两个单独的CONTAINS查询,但我不知道从两个搜索查询中提取组合排名的方法.例如,如果我搜索"Arthur",则应考虑Book查询和Author查询的结果并相应地加权.

Ishmael.. 15

使用FREETEXTTABLE,您只需设计一些算法来计算每个连接表结果的合并排名.下面的示例将结果倾向于书表中的命中.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]
FROM Book b
INNER JOIN Author a ON b.AuthorID = a.AuthorID
INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] 
LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]
ORDER BY [Rank] DESC

请注意,我简化了此示例的架构.

1> Ishmael..：

---

使用FREETEXTTABLE,您只需设计一些算法来计算每个连接表结果的合并排名.下面的示例将结果倾向于书表中的命中.

SELECT b.Name, a.Name, bkt.[Rank] + akt.[Rank]/2 AS [Rank]
FROM Book b
INNER JOIN Author a ON b.AuthorID = a.AuthorID
INNER JOIN FREETEXTTABLE(Book, Name, @criteria) bkt ON b.ContentID = bkt.[Key] 
LEFT JOIN FREETEXTTABLE(Author, Name, @criteria) akt ON a.AuthorID = akt.[Key]
ORDER BY [Rank] DESC

请注意,我简化了此示例的架构.

---

不确定`设计一些算法来计算合并等级'是多么可行.[文档](https://technet.microsoft.com/en-us/library/cc879245.aspx)说:"排名值仅表示**结果集中行的相关性的相对顺序**,较低的值表示相关性较低.实际值不重要,通常每次运行查询时都不同." 你必须将等级公式(OKAPI BM25)标准化,这似乎是可疑的,因为绝对等级值将随着基础指数统计而变化.

2> 小智..：

---

我遇到了与您相同的问题，但实际上涉及10个表（一个Users表和其他几个表）

我在每个表的WHERE子句中使用FREETEXT进行了第一个查询，但查询时间太长。

然后，我看到了一些有关改用FREETEXTTABLE并在每个表的键列中检查非null值的答复，但这花费了很长时间才能执行。

我通过结合使用FREETEXTTABLE和UNION选择来解决此问题：

SELECT Users.* FROM Users INNER JOIN
(SELECT Users.UserId FROM Users INNER JOIN FREETEXTTABLE(Users, (column1, column2), @variableWithSearchTerm) UsersFT ON Users.UserId = UsersFT.key
UNION
SELECT Table1.UserId FROM Table1 INNER JOIN FREETEXTTABLE(Table1, TextColumn, @variableWithSearchTerm) Table1FT ON Table1.UserId = Table1FT.key
UNION
SELECT Table2.UserId FROM Table2 INNER JOIN FREETEXTTABLE(Table2, TextColumn, @variableWithSearchTerm) Table2FT ON Table2.UserId = Table2FT.key
... --same for all tables
) fts ON Users.UserId = fts.UserId

事实证明，这要快得多。

希望对您有所帮助。