SQL Server评分搜索开发：求多数据表合并计算评分方法

Hey there, let's work through this together. You're building a "friendly search" feature in SQL Server where you need to both retrieve matching entries and calculate a relevance score in one query, and you're stuck on merging results from multiple tables into a single set with that final score. Here's how to pull this off:

Core Approach #

The key idea is to calculate relevance scores for each table individually, then combine all those results into a unified dataset. We'll cover two practical methods—one with custom scoring logic, and another using SQL Server's built-in full-text search for better performance and accuracy.

Method 1: Custom Scoring with UNION ALL #

First, let's set up a way to split your search keywords into individual terms (since you referenced string-splitting resources, here's a simple table-valued function for that):

CREATE FUNCTION dbo.SplitKeywords(@Keywords NVARCHAR(MAX))
RETURNS @KeywordTable TABLE (Keyword NVARCHAR(100))
AS
BEGIN
    DECLARE @Delimiter CHAR(1) = ' '; -- Adjust if your keywords use commas or another separator
    DECLARE @Start INT = 1, @End INT;

    -- Add delimiter to the end if missing to handle the final keyword
    IF SUBSTRING(@Keywords, LEN(@Keywords), 1) <> @Delimiter
        SET @Keywords = @Keywords + @Delimiter;

    WHILE CHARINDEX(@Delimiter, @Keywords, @Start) > 0
    BEGIN
        SET @End = CHARINDEX(@Delimiter, @Keywords, @Start);
        INSERT INTO @KeywordTable (Keyword)
        VALUES (LTRIM(RTRIM(SUBSTRING(@Keywords, @Start, @End - @Start))));
        SET @Start = @End + 1;
    END;
    RETURN;
END;

Next, write a query that calculates scores for each table, then merges them with UNION ALL. We'll assign weights (e.g., higher scores for title matches vs. description matches) to make relevance more meaningful:

-- Retrieve and score results from the Products table
SELECT 
    'Product' AS SourceType,
    ProductId AS ItemId,
    Title,
    Description,
    -- Custom scoring: 2 points for title matches, 1 for description matches
    SUM(
        CASE WHEN CHARINDEX(k.Keyword, p.Title) > 0 THEN 2 ELSE 0 END +
        CASE WHEN CHARINDEX(k.Keyword, p.Description) > 0 THEN 1 ELSE 0 END
    ) AS RelevanceScore
FROM Products p
CROSS APPLY dbo.SplitKeywords('your search terms here') k
GROUP BY ProductId, Title, Description
-- Filter out entries that don't match any keywords
HAVING SUM(
    CASE WHEN CHARINDEX(k.Keyword, p.Title) > 0 OR CHARINDEX(k.Keyword, p.Description) > 0 THEN 1 ELSE 0 END
) > 0

UNION ALL

-- Repeat the pattern for your Articles table
SELECT 
    'Article' AS SourceType,
    ArticleId AS ItemId,
    Title,
    Content AS Description,
    SUM(
        CASE WHEN CHARINDEX(k.Keyword, a.Title) > 0 THEN 2 ELSE 0 END +
        CASE WHEN CHARINDEX(k.Keyword, a.Content) > 0 THEN 1 ELSE 0 END
    ) AS RelevanceScore
FROM Articles a
CROSS APPLY dbo.SplitKeywords('your search terms here') k
GROUP BY ArticleId, Title, Content
HAVING SUM(
    CASE WHEN CHARINDEX(k.Keyword, a.Title) > 0 OR CHARINDEX(k.Keyword, a.Content) > 0 THEN 1 ELSE 0 END
) > 0

-- Add more UNION ALL blocks for additional tables (e.g., Videos, Blogs)

-- Sort final results by relevance (highest first)
ORDER BY RelevanceScore DESC;

Method 2: Using SQL Server Full-Text Search (Better for Large Datasets) #

If you're working with large tables, custom string matching can get slow. SQL Server's full-text search has built-in relevance ranking and is optimized for search use cases. First, make sure you've set up full-text indexes on the columns you want to search. Then use this query:

-- Products results with full-text relevance score
SELECT 
    'Product' AS SourceType,
    ProductId AS ItemId,
    Title,
    Description,
    KEY_TBL.RANK AS RelevanceScore
FROM Products p
INNER JOIN FREETEXTTABLE(Products, (Title, Description), 'your search terms here') KEY_TBL
    ON p.ProductId = KEY_TBL.[KEY]

UNION ALL

-- Articles results with full-text relevance score
SELECT 
    'Article' AS SourceType,
    ArticleId AS ItemId,
    Title,
    Content AS Description,
    KEY_TBL.RANK AS RelevanceScore
FROM Articles a
INNER JOIN FREETEXTTABLE(Articles, (Title, Content), 'your search terms here') KEY_TBL
    ON a.ArticleId = KEY_TBL.[KEY]

-- Add more UNION ALL blocks for other tables

ORDER BY RelevanceScore DESC;

The RANK column here is calculated automatically by SQL Server based on how well the entry matches your search terms—no need to write custom scoring logic!

Key Tips #

• Use UNION ALL instead of UNION because UNION removes duplicates (which you probably don't want here) and runs slower.
• Adjust the scoring weights or full-text search parameters to match your specific "friendly search" needs (e.g., prioritize exact matches, or certain fields like product names).
• If your tables have different columns, standardize the output columns (like we did with SourceType, ItemId, Title, Description) so the UNION ALL works correctly.

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