SQL查询：同公司下emailAddress与其他用户username重复的行

需求回顾 #

需要从临时表#TestEmailTable中筛选出符合以下条件的行：

• 同属一个companyId
• 当前行的username值，出现在同一companyId下其他行的emailAddress字段中（同一行的username和emailAddress相等不算）

原SQL问题分析 #

你写的SQL逻辑方向反了：子查询统计的是companyId+username的出现次数，关联条件是a.emailAddress = b.username，这是在找「当前行的emailAddress等于某个username」的记录，和你要找的「当前行的username被其他行的emailAddress引用」完全相反，而且没排除自身行的干扰，所以得不到正确结果。

正确解法 #

方法1：用EXISTS子查询（推荐，性能更优）

SELECT 
    companyId,
    username,
    emailAddress,
    1 AS hasADuplicate
FROM #TestEmailTable t
WHERE EXISTS (
    SELECT 1
    FROM #TestEmailTable t2
    WHERE t2.companyId = t.companyId
      AND t2.emailAddress = t.username
      -- 排除自身行：因为没有主键，用字段组合判断不是同一行
      AND NOT (t2.username = t.username AND t2.emailAddress = t.emailAddress)
)

方法2：用JOIN+DISTINCT

SELECT DISTINCT
    t.companyId,
    t.username,
    t.emailAddress,
    1 AS hasADuplicate
FROM #TestEmailTable t
JOIN #TestEmailTable t2 
    ON t2.companyId = t.companyId
    AND t2.emailAddress = t.username
    -- 排除自身行
    AND NOT (t2.username = t.username AND t2.emailAddress = t.emailAddress)

结果验证 #

运行上述SQL后，会得到你期望的结果：

说明 #

• EXISTS子查询会高效检查是否存在符合条件的其他行，只要找到匹配就停止检索，性能比JOIN更优
• 因为临时表没有主键，所以用username和emailAddress的组合来排除自身行，如果后续表有主键（比如id），直接用t2.id != t.id会更准确

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