如何使用SQL Server的CONTAINS函数在列中搜索多值？

我创建了包含搜索项的临时表：

DROP TABLE IF EXISTS #searchitems

SELECT 'Road' as item
INTO #searchitems
UNION 
SELECT 'Bike'

搜索项为Road和Bike，已给Name列创建全文索引，尝试用以下语句查询包含这些项的数据行：

SELECT Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, (Select item FROM #searchitems) )

但执行失败，单个值时可正常运行，期望实现类似以下语句的效果：

SELECT distinct Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, 'Road OR Bike' )

解答 #

CONTAINS函数本身不直接支持传入多值列表作为搜索参数，它要求第二个参数是符合全文搜索语法的单个字符串（比如用OR/AND连接的搜索项组合）。要实现从临时表取多值搜索的需求，可以用以下两种方案：

方案1：动态SQL拼接搜索字符串 #

将临时表中的搜索项拼接成"Road" OR "Bike"格式的字符串，再传入CONTAINS：

DECLARE @SearchString NVARCHAR(MAX)

-- SQL Server 2017及以上版本用STRING_AGG拼接
SELECT @SearchString = STRING_AGG(QUOTENAME(item, '"'), ' OR ')
FROM #searchitems

-- 执行动态查询
EXEC sp_executesql N'
SELECT DISTINCT Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, @SearchStr)
', N'@SearchStr NVARCHAR(MAX)', @SearchStr = @SearchString

如果是SQL Server 2016及更早版本，可改用FOR XML PATH拼接字符串：

DECLARE @SearchString NVARCHAR(MAX)
SELECT @SearchString = STUFF(
    (SELECT ' OR ' + QUOTENAME(item, '"') 
     FROM #searchitems 
     FOR XML PATH('')), 1, 4, ''
)

EXEC sp_executesql N'
SELECT DISTINCT Name
FROM [AdventureWorksLT2019].[SalesLT].[Product]
WHERE CONTAINS(*, @SearchStr)
', N'@SearchStr NVARCHAR(MAX)', @SearchStr = @SearchString

方案2：通过JOIN关联临时表 #

不需要拼接字符串，直接将产品表与临时表通过CONTAINS关联：

SELECT DISTINCT p.Name
FROM [AdventureWorksLT2019].[SalesLT].[Product] p
INNER JOIN #searchitems s 
    ON CONTAINS(p.*, s.item)

这种方式逻辑更直观，但如果临时表中搜索项数量较多，性能可能不如方案1（因为需要对每个搜索项单独执行一次CONTAINS匹配）。

内容的提问来源于stack exchange，提问作者Petersselorse