我可以在表格中使用全文搜索，其中条件是来自选择查询的值数组吗？

I want to create a view and I want to do a full text search of a row using a set of keywords. These keywords exist in a table in the database.

我想创建一个视图,我想使用一组关键字对行进行全文搜索。这些关键字存在于数据库的表中。

So is it possible to do something like below where I can use a select statement to dynamically determine which keywords to filter on.

因此可以执行类似下面的操作,我可以使用select语句动态确定要过滤的关键字。

SELECT * FROM table1
WHERE CONTAINS(Row1, 
'[SELECT k.Name FROM KeywordCategory kc
inner join Keyword k
on kc.KeywordId = k.Id
where kc.Category in ('BrandA', 'BrandB', 'BrandC')]')

2 个解决方案

#1

The CONTAINS search condition cannot reference other tables, but you can get around this limitation by constructing a variable from the keywords.

CONTAINS搜索条件不能引用其他表,但您可以通过从关键字构造变量来解决此限制。

-- build search condition, example: '"keyword1" OR "keyword2" OR "keyword3"'
declare @SearchCondition nvarchar(4000)
SELECT  @SearchCondition = IsNull(@SearchCondition + ' OR ', '') + '"' + k.Name + '"'
FROM KeywordCategory kc
inner join Keyword k on kc.KeywordId = k.Id
where kc.Category in ('BrandA', 'BrandB', 'BrandC')

SELECT  *
FROM    table1
WHERE   Contains(*, @SearchCondition)

You won't be able to do this in a view though, so you would have to write it as a function or stored procedure.

但是您无法在视图中执行此操作,因此您必须将其作为函数或存储过程编写。

#2

Hi Yes you can use contains with the full text search indexed column. If you want to have two words one near the other you can use contains 'keyboard1' near 'keyboard2' etc

您好是您可以使用包含全文搜索索引列的包含。如果你想在另一个附近有两个单词,你可以使用'keyboard2'附近的'keyboard1'等

#1