SQL联表查询如何排除Self Service特定匹配记录

匹配到Self Service无效记录的核心原因是关联Employee表时没有提前过滤无效团队记录，且多轮同表关联的写法没有控制匹配优先级。可按以下方式修正：

核心调整逻辑 #

• 关联员工表时直接排除Team = 'Self Service'的记录，从关联层避免无效值匹配
• 用OUTER APPLY替代原有4次重复同表LEFT JOIN的写法，按「先匹配DisplayName、再匹配PrimaryEmail」的优先级取有效团队值，减少冗余关联
• 给WHERE子句中的时间判断条件加括号，修正SQL默认AND优先级高于OR导致的范围筛选逻辑错误

修正后可直接运行的代码 #

SELECT 
    i.CreatedBy, 
    cb.Team AS CreatedByTeam, 
    i.CreatedDateTime, 
    i.Owner, 
    i.OwnerTeam, 
    i.LastModDateTime, 
    i.ResolvedBy,
    rb.Team AS ResolvedByTeam
FROM 
    Incident i
    -- 匹配创建人所属有效团队
    OUTER APPLY (
        SELECT TOP 1 Team
        FROM Employee e
        WHERE 
            e.Team != 'Self Service'
            AND (e.DisplayName = i.CreatedBy OR e.PrimaryEmail = i.CreatedBy)
        -- 控制匹配优先级：DisplayName匹配优先于PrimaryEmail匹配
        ORDER BY CASE WHEN e.DisplayName = i.CreatedBy THEN 0 ELSE 1 END
    ) cb
    -- 匹配解决人所属有效团队
    OUTER APPLY (
        SELECT TOP 1 Team
        FROM Employee e
        WHERE 
            e.Team != 'Self Service'
            AND (e.DisplayName = i.ResolvedBy OR e.PrimaryEmail = i.ResolvedBy)
        ORDER BY CASE WHEN e.DisplayName = i.ResolvedBy THEN 0 ELSE 1 END
    ) rb
WHERE 
    (i.CreatedDateTime BETWEEN '2021-06-09' AND '2022-06-09') 
    OR (i.LastModDateTime BETWEEN '2021-06-09' AND '2022-06-09')

最小改动兼容方案 #

如果不想调整原有JOIN结构，只需要在每一次关联Employee表的ON条件后追加无效团队过滤规则即可，修改示例：

SELECT 
    i.CreatedBy, 
    COALESCE (e.Team, e1.Team) as CreatedByTeam, 
    i.CreatedDateTime, 
    i.Owner, 
    i.OwnerTeam, 
    i.LastModDateTime, 
    i.ResolvedBy,
    COALESCE (e2.Team, e3.Team) as ResolvedByTeam
FROM 
    Incident i
    LEFT JOIN Employee e ON i.CreatedBy=e.DisplayName AND e.Team != 'Self Service'
    LEFT JOIN Employee e1 ON i.CreatedBy=e1.PrimaryEmail AND e1.Team != 'Self Service'
    LEFT JOIN Employee e2 ON i.ResolvedBy=e2.DisplayName AND e2.Team != 'Self Service'
    LEFT JOIN Employee e3 ON i.ResolvedBy=e3.PrimaryEmail AND e3.Team != 'Self Service'
WHERE 
    (i.CreatedDateTime BETWEEN '2021-06-09' AND '2022-06-09') OR (i.LastModDateTime BETWEEN '2021-06-09' AND '2022-06-09')

注意：如果某员工在Employee表中仅存在Self Service对应的记录，上述两种写法都会返回NULL作为团队值，符合排除无效记录的预期。

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