SQL Server全文搜索—将某些列与其他列进行加权

If I have the following full text search query:

如果我有以下全文搜索查询:

SELECT *
FROM dbo.Product
   INNER JOIN CONTAINSTABLE(Product, (Name, Description, ProductType), 'model') ct
      ON ct.[Key] = Product.ProductID

Is it possible to weigh the columns that are being searched?

是否有可能对正在搜索的列进行称重?

For example, I care more about the word model appearing in the Name column than I do the Description or ProductType columns.

例如，我更关心出现在Name列中的单词模型，而不是描述或ProductType列。

Of course if the word is in all 3 columns then I would expect it to rank higher than if it was just in the name column. Is there any way to have a row rank higher if it just appears in Name vs just in Description/ProductType?

当然，如果这个词在所有的三列中，那么我希望它的排名比它在name列中的排名要高。如果行只是在名称中出现，而不是在Description/ProductType中出现，是否有可能使行级别更高?

2 个解决方案

#1

You can do something like the following query. Here, WeightedRank is computed by multiplying the rank of the individual matches. NOTE: unfortunately I don't have Northwind installed so I couldn't test this, so look at it more like pseudocode and let me know if it doesn't work.

您可以执行如下查询。在这里，称量法是通过将单个火柴的等级相乘来计算的。注意:不幸的是，我没有安装Northwind，所以我无法测试它，所以更像查看伪代码，并让我知道它是否不起作用。

declare @searchTerm varchar(50) = 'model';

 SELECT 100 * coalesce(ct1.RANK, 0) +
        10 * coalesce(ct2.RANK, 0) +
        1 * coalesce(ct3.RANK, 0) as WeightedRank,
        *
   FROM dbo.Product
            LEFT JOIN
        CONTAINSTABLE(Product, Name, @searchTerm) ct1 ON ct.[Key] = Product.ProductID
            LEFT JOIN
        CONTAINSTABLE(Product, Description, @searchTerm) ct2 ON ct.[Key] = Product.ProductID
            LEFT JOIN
        CONTAINSTABLE(Product, ProductType, @searchTerm) ct3 ON ct.[Key] = Product.ProductID

#2

Listing 3-25. Sample Column Rank-Multiplier Search of Pro Full-Text Search in SQL Server 2008

清单3-25。SQL Server 2008中专业全文搜索的列排序乘数搜索示例

SELECT *
FROM (
    SELECT Commentary_ID
        ,SUM([Rank]) AS Rank
    FROM (
        SELECT bc.Commentary_ID
            ,c.[RANK] * 10 AS [Rank]
        FROM FREETEXTTABLE(dbo.Contributor_Birth_Place, *, N'England') c
        INNER JOIN dbo.Contributor_Book cb ON c.[KEY] = cb.Contributor_ID
        INNER JOIN dbo.Book_Commentary bc ON cb.Book_ID = bc.Book_ID

        UNION ALL

        SELECT c.[KEY]
            ,c.[RANK] * 5
        FROM FREETEXTTABLE(dbo.Commentary, Commentary, N'England') c

        UNION ALL

        SELECT ac.[KEY]
            ,ac.[RANK]
        FROM FREETEXTTABLE(dbo.Commentary, Article_Content, N'England') ac
        ) s
    GROUP BY Commentary_ID
    ) s1
INNER JOIN dbo.Commentary c1 ON c1.Commentary_ID = s1.Commentary_ID
ORDER BY [Rank] DESC;