LEFT JOIN关联CHARINDEX匹配重复行：如何实现仅返回单行？

哈哈，这个多Bot词匹配导致重复行的问题我太熟了！之前做日志分析的时候天天跟这个打交道。咱们根据你的需求——既要标记Bot行、保留非Bot行，又要避免匹配多词汇时的重复——给你几个靠谱的解决方案：

方案一：用EXISTS做Bot标记（最简洁高效） #

如果你的核心需求只是判断是否是Bot行，不需要知道具体匹配了哪个Bot词汇，那用EXISTS是最优解。它只会返回「存在匹配/不存在匹配」的布尔结果，完全不会产生重复行：

SELECT 
    UE.EventDate, 
    UE.EventType, 
    UE.Institution_Party_id, 
    UE.IP, 
    UE.DOI, 
    UE.Usage_Count, 
    UE.User_Agent,
    -- 标记是否为Bot：1=是，0=否
    CASE WHEN EXISTS (
        SELECT 1 
        FROM DWH.BOT_Word_List BWL 
        WHERE CHARINDEX(BWL.Word, UE.User_Agent) != 0
    ) THEN 1 ELSE 0 END AS IsBot
FROM [STG].[TABLE_UE] UE 
WHERE UE.DOI IS NOT NULL AND UE.Institution_Party_Id IS NOT NULL 
ORDER BY UE.EventDate, UE.EventType, UE.Institution_Party_id, UE.IP, UE.DOI, UE.Usage_Count, UE.User_Agent;

为什么这个方案好用？ #

EXISTS是半连接查询，只要找到第一个匹配的Bot词就会停止检索，性能比LEFT JOIN好；而且不管匹配多少个词，原表的每一行只会返回一次，完美解决重复问题。

方案二：用OUTER APPLY获取单个匹配的Bot词（需知道具体匹配词时用） #

如果业务上需要知道具体匹配了哪个Bot词汇，但只需要保留一个（比如优先匹配更长的词），可以用OUTER APPLY搭配TOP 1：

SELECT 
    UE.EventDate, 
    UE.EventType, 
    UE.Institution_Party_id, 
    UE.IP, 
    UE.DOI, 
    UE.Usage_Count, 
    UE.User_Agent,
    -- 返回第一个匹配的Bot词，无匹配则为NULL
    BWL.Word AS MatchedBotWord,
    CASE WHEN BWL.Word IS NOT NULL THEN 1 ELSE 0 END AS IsBot
FROM [STG].[TABLE_UE] UE 
OUTER APPLY (
    SELECT TOP 1 Word 
    FROM DWH.BOT_Word_List BWL 
    WHERE CHARINDEX(BWL.Word, UE.User_Agent) != 0
    -- 可选：按词汇长度倒序，优先匹配更长的词（比如先匹配"Googlebot"而非"bot"）
    ORDER BY LEN(BWL.Word) DESC
) BWL
WHERE UE.DOI IS NOT NULL AND UE.Institution_Party_Id IS NOT NULL 
ORDER BY UE.EventDate, UE.EventType, UE.Institution_Party_id, UE.IP, UE.DOI, UE.Usage_Count, UE.User_Agent;

这个方案的优势： #

既保留了原表的所有行（非Bot行也能返回），又确保每个Bot行只返回一次，还能拿到具体的匹配词汇。如果需要控制匹配优先级，加个ORDER BY就能搞定。

方案三：用GROUP BY聚合（需统计匹配数量时用） #

如果需要知道一行匹配了多少个Bot词汇，同时合并重复行，可以用GROUP BY：

SELECT 
    UE.EventDate, 
    UE.EventType, 
    UE.Institution_Party_id, 
    UE.IP, 
    UE.DOI, 
    UE.Usage_Count, 
    UE.User_Agent,
    -- 统计匹配的Bot词数量
    COUNT(BWL.Word) AS BotMatchCount,
    CASE WHEN COUNT(BWL.Word) > 0 THEN 1 ELSE 0 END AS IsBot
FROM [STG].[TABLE_UE] UE 
LEFT JOIN DWH.BOT_Word_List BWL ON CHARINDEX(BWL.Word, UE.User_Agent) != 0
WHERE UE.DOI IS NOT NULL AND UE.Institution_Party_Id IS NOT NULL 
GROUP BY UE.EventDate, UE.EventType, UE.Institution_Party_id, UE.IP, UE.DOI, UE.Usage_Count, UE.User_Agent
ORDER BY UE.EventDate, UE.EventType, UE.Institution_Party_id, UE.IP, UE.DOI, UE.Usage_Count, UE.User_Agent;

注意事项： #

这个方案要求GROUP BY后的字段组合能唯一标识原表的行，如果原表存在这些字段完全相同的重复行，会被合并。如果你的业务场景中这些字段是唯一的，那这个方案完全没问题。

总结选择建议： #

• 仅需Bot标记 → 选方案一（性能最优，代码最简洁）
• 需要知道具体匹配的Bot词 → 选方案二（灵活可控）
• 需要统计匹配数量 → 选方案三（满足统计需求）

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