SQL存储过程用临时表和内连接查询出现重复行的解决方法

原存储过程的问题分析 #

1. 重复行泛滥：当传入多关键词时，两次内连接临时表@SearchTable会产生笛卡尔积。比如3个关键词会生成3×3=9倍的重复数据，导致结果行数远超预期。
2. 结果漏判：原逻辑要求行的title匹配任意关键词，同时questionText匹配任意关键词，这就漏掉了那些仅title包含所有关键词或仅questionText包含所有关键词的行，比如测试场景中本该返回11行，却只返回1行。

修复方案 #

方案1：匹配所有关键词（AND逻辑） #

此方案确保传入的每个关键词都出现在title或questionText中，同时返回唯一的行：

ALTER PROCEDURE [dbo].[getThreadsBySearchTerm] 
    @searchTerm nvarchar(80)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SearchTable TABLE(searchTerm VARCHAR(MAX))

    -- 拆分关键词并去重，过滤空值（避免多空格导致的无效关键词）
    INSERT INTO @SearchTable (searchTerm)
    SELECT DISTINCT value 
    FROM STRING_SPLIT(@searchTerm, ' ')
    WHERE TRIM(value) <> '';

    -- 如果没有有效关键词，返回空结果
    IF (SELECT COUNT(*) FROM @SearchTable) = 0
    BEGIN
        SELECT title, questionText FROM tblThread WHERE 1=0;
        RETURN;
    END

    -- 匹配所有关键词，返回唯一行
    SELECT t.title, t.questionText
    FROM tblThread t
    INNER JOIN @SearchTable st 
        ON t.title LIKE '%' + st.searchTerm + '%' 
        OR t.questionText LIKE '%' + st.searchTerm + '%'
    GROUP BY t.title, t.questionText
    HAVING COUNT(DISTINCT st.searchTerm) = (SELECT COUNT(*) FROM @SearchTable);
END
GO

方案2：匹配任意关键词（OR逻辑） #

如果需求是只要有一个关键词匹配就返回，可改用以下逻辑：

ALTER PROCEDURE [dbo].[getThreadsBySearchTerm] 
    @searchTerm nvarchar(80)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SearchTable TABLE(searchTerm VARCHAR(MAX))

    INSERT INTO @SearchTable (searchTerm)
    SELECT DISTINCT value 
    FROM STRING_SPLIT(@searchTerm, ' ')
    WHERE TRIM(value) <> '';

    IF (SELECT COUNT(*) FROM @SearchTable) = 0
    BEGIN
        SELECT title, questionText FROM tblThread WHERE 1=0;
        RETURN;
    END

    SELECT DISTINCT t.title, t.questionText
    FROM tblThread t
    INNER JOIN @SearchTable st 
        ON t.title LIKE '%' + st.searchTerm + '%' 
        OR t.questionText LIKE '%' + st.searchTerm + '%';
END
GO

修复说明 #

• 新增关键词去重和空值过滤：避免输入多空格或重复关键词导致的无效计算。
• 用GROUP BY+COUNT(DISTINCT)确保所有关键词都被匹配（AND逻辑），彻底解决重复行问题。
• 用DISTINCT（OR逻辑）或GROUP BY（AND逻辑）保证返回结果唯一。

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