SQL Server 全文搜索 - 对某些列进行加权

Question

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

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

是否可以对正在搜索的列进行权重？

例如，我更关心“名称”列中出现的“模型”一词，而不是“ 描述或产品类型列。

当然，如果该单词出现在所有 3 列中，那么我预计它的排名会比仅出现在名称列中时更高。如果行仅出现在名称中而不是仅出现在描述/产品类型中，是否有任何方法可以使其排名更高？

Answer 1

您可以执行类似以下查询的操作。这里，WeightedRank 是通过乘以各个匹配的排名来计算的。注意：不幸的是，我没有安装 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 ct1.[Key] = Product.ProductID
            LEFT JOIN
        CONTAINSTABLE(Product, Description, @searchTerm) ct2 ON ct2.[Key] = Product.ProductID
            LEFT JOIN
        CONTAINSTABLE(Product, ProductType, @searchTerm) ct3 ON ct3.[Key] = Product.ProductID 
 order by WeightedRank desc

Answer 2

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

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;

Answer 3

与亨利的解决方案类似，但经过简化、测试并使用问题提供的详细信息。

注意：我对联合和左连接样式进行了性能测试，发现下面的联合样式与我的数据集 YMMV 所需的逻辑读取要少得多。

 declare @searchTerm varchar(50) = 'model';
 declare @nameWeight int = 100;
 declare @descriptionWeight int = 10;
 declare @productTypeWeight int = 1;
 
 SELECT ranksGroupedByProductID.*, outerProduct.*
FROM   (SELECT [key],
               Sum([rank]) AS WeightedRank 
        FROM   (
                -- Each column that needs to be weighted separately 
                -- should be added here and unioned with the other queries
                SELECT [key],
                       [rank] * @nameWeight as [rank]
                FROM   Containstable(dbo.Product, [Name], @searchTerm) 
                       
                UNION ALL
                
                SELECT [key],
                       [rank] * @descriptionWeight as [rank]
                FROM   Containstable(dbo.Product, [Description], @searchTerm) 
                       
                UNION ALL

                SELECT [key],
                       [rank] * @productTypeWeight as [rank]
                FROM   Containstable(dbo.Product, [ProductType], @searchTerm) 
                       
                ) innerSearch

       -- Grouping by key allows us to sum each ProductID's ranks for all the columns
        GROUP  BY [key]) ranksGroupedByProductID

        -- This join is just to get the full Product table columns 
        -- and is optional if you only need the ordered ProductIDs
       INNER JOIN dbo.Product outerProduct
               ON outerProduct.ProductID = ranksGroupedByProductID.[key]

ORDER  BY WeightedRank DESC;