包含包含表的子查询以及来自查询表的条件

debugcn 发表于 Dev

莫森

该查询给了我错误：“'建议'附近的语法不正确”。

SELECT(SELECT COUNT(*) FROM CONTAINSTABLE(Products,keywords,Suggestion)) InventoryRank
FROM (
    SELECT Suggestion FROM aTable
)

我如何得出相同的结果？

gofr1

CONTAINSTABLE不能在搜索条件下与表列一起使用。您可以像这样：

DECLARE @search nvarchar(4000)

SELECT @search = STUFF(( 
    SELECT  '*" or "' + Suggestion 
    FROM aTable
    FOR XML PATH('')
),1,6,'') +'*"'

--That will give you string like "sug1*" or "sug2*" or "sug3*" to search
--You can make whatever you need string. F.e. "sug1" or "sug2" or "sug3"

SELECT COUNT (*) 
FROM CONTAINSTABLE(searched_table,searched_column,@search)

编辑

如果需要不同的InventoryRankper Suggestion，则可以使用动态SQL和临时表：

IF OBJECT_ID(N'##temp') IS NOT NULL DROP TABLE ##temp

CREATE TABLE ##temp (
    Suggestion nvarchar(max),
    InventoryRank int
)

DECLARE @sql nvarchar(max)


SELECT @sql = (
    SELECT N'INSERT INTO ##temp SELECT  '''+Suggestion+''' as Suggestion, COUNT (*) as InventoryRank FROM CONTAINSTABLE(searched_table,searched_column,'''+Suggestion+''');' 
    FROM aTable
    FOR XML PATH('')
)
--PRINT(@sql)

EXEC sp_executesql @sql

SELECT *
FROM ##temp

PRINT将给您查询：

INSERT INTO ##temp SELECT  'sug1' as Suggestion, COUNT (*) as InventoryRank FROM CONTAINSTABLE(searched_table,searched_column,'sug1');
INSERT INTO ##temp SELECT  'sug2' as Suggestion, COUNT (*) as InventoryRank FROM CONTAINSTABLE(searched_table,searched_column,'sug2');

因此，您不需要WHILE循环或CURSOR即可遍历 aTable

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。