I'm working on a project with SQL Server 2008 where I believe full-text search is the best way to go. I've been reading up on it as much as I can, and pretty much understand hos to set it up for a single table. However I'm not entirely sure how to set it up with my scenario - imagine the following table structure:
我正在开发一个SQL Server 2008项目，我相信全文搜索是最好的方法。我一直在阅读它，并且非常了解如何设置单个表格。但是我不完全确定如何使用我的场景进行设置 - 想象下面的表格结构：
Book
书
- Id
- Title
- Description
BookAuthor
BOOKAUTHOR
- BookId
- AuthorId
Author
作者
- Id
- Name
As you can see, the database holds a table with books, and each book can have none, one or many authors. Each author can also be part of none, one or many books - i.e. the Book and Author tables have a many-to-many relationship, handled with the linking table BookAuthor.
如您所见，数据库包含一个包含书籍的表格，每本书可以没有，一个或多个作者。每个作者也可以是无，一本或多本书的一部分 - 即书和作者表具有多对多关系，用链接表BookAuthor处理。
What I want to accomplish at this point is a search tool to find matching books based on a search string the user provides. So if the user types in Brown I would want to find all books where either of the following columns contains the word Brown:
此时我想要完成的是一种搜索工具，可根据用户提供的搜索字符串查找匹配的书籍。因此，如果用户输入Brown，我会想要查找以下任一列包含单词Brown的所有书籍：
Book.Title
Book.Description
Author.Name
In essence, I want a result set of books, including both the book with the title Brown Bear and the books written by author Dan Brown. If there are any suggestions out there as to how I should set this up, I'd really appreciate your input!
本质上，我想要一套结果书，包括标题为Brown Bear的书和作者Dan Brown写的书。如果有任何建议我应该如何设置，我真的很感激你的意见！
(as a side note, once I have this filtering working, the query result would also need to be sortable and pageable, handled via @SortOrder, @PageIndex and @PageSize passed into a Stored Procedure - but I guess that could be a separate question afterwards!)
（作为旁注，一旦我进行了这个过滤，查询结果也需要是可排序和可分页的，通过@SortOrder处理，@ PageIndex和@PageSize传递到存储过程 - 但我想这可能是一个单独的问题之后！）
1 个解决方案
#1
2
A CONTAINS predicate can take a list of columns to search as its first argument; however, these columns must be from a single table. You have a couple of options for getting around this limitation. One option is that you can perform two separate searches, one on each table, and then UNION the results together.
CONTAINS谓词可以将列的列表作为其第一个参数进行搜索;但是，这些列必须来自单个表。您可以通过几种方法来解决此限制。一种选择是您可以执行两个单独的搜索，每个表上一个，然后将结果联合起来。
select Id, Title from Book where contains([Description], 'brown')
union
select b.Id, b.Title
from Book b inner join BookAuthor ba on b.Id = ba.BookId
inner join Author a on a.Id=ba.AuthorId
where contains([Name], 'brown')
Another option is to take advantage of the fact that FTS indexes can be created on indexed views. To do this, create an indexed view that contains both the Title field from the Book table and the Name field from the Author table, and then create a FTS index on both of these columns in the view. Then you can write queries against this view as follows:
另一种选择是利用可以在索引视图上创建FTS索引的事实。为此，请创建一个索引视图，其中包含Book表中的Title字段和Author表中的Name字段，然后在视图中的这两列上创建FTS索引。然后，您可以针对此视图编写查询，如下所示：
select BookId, Title from vw_BooksAndAuthors
where contains(([Description], [Name]), 'brown')
#1
2
A CONTAINS predicate can take a list of columns to search as its first argument; however, these columns must be from a single table. You have a couple of options for getting around this limitation. One option is that you can perform two separate searches, one on each table, and then UNION the results together.
CONTAINS谓词可以将列的列表作为其第一个参数进行搜索;但是，这些列必须来自单个表。您可以通过几种方法来解决此限制。一种选择是您可以执行两个单独的搜索，每个表上一个，然后将结果联合起来。
select Id, Title from Book where contains([Description], 'brown')
union
select b.Id, b.Title
from Book b inner join BookAuthor ba on b.Id = ba.BookId
inner join Author a on a.Id=ba.AuthorId
where contains([Name], 'brown')
Another option is to take advantage of the fact that FTS indexes can be created on indexed views. To do this, create an indexed view that contains both the Title field from the Book table and the Name field from the Author table, and then create a FTS index on both of these columns in the view. Then you can write queries against this view as follows:
另一种选择是利用可以在索引视图上创建FTS索引的事实。为此，请创建一个索引视图，其中包含Book表中的Title字段和Author表中的Name字段，然后在视图中的这两列上创建FTS索引。然后，您可以针对此视图编写查询，如下所示：
select BookId, Title from vw_BooksAndAuthors
where contains(([Description], [Name]), 'brown')