且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

在多个表和列中使用SQL Server 2008中的全文本搜索

更新时间:2022-11-28 15:14:59

Using FREETEXTTABLE, you just need to design some algorithm to calculate the merged rank on each joined table result. The example below skews the result towards hits from the book table.

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

Note that I simplified your schema for this example.