SQL内连接查询如何按指定字段仅筛选获取重复记录

实现思路 #

通过分组计数的方式识别重复记录：

• 以EMailAddress、FirstName、LastName、DOB四个字段作为重复判定的分组依据
• 统计每个分组内的记录总数，仅保留总数大于1的分组下的所有记录
  推荐使用窗口函数实现，代码更简洁、执行效率更高。

可直接运行的修改后SQL #

注：原查询SELECT语句中未包含p.[DOB]字段，已补上用于重复判定，不需要返回该字段可在外层SELECT中移除。

WITH BaseQuery AS (
    SELECT 
        p.CustomerNumber
        ,pn.[Title]
        ,pn.[FirstName]
        ,pn.[LastName]
        ,a.[AgentID]
        ,a.[AgentName]
        ,a.[PersonID]
        ,pe.[EMailAddress]
        ,p.[DOB]
        ,(SELECT TOP 1 pp.[PhoneCode] FROM [Rez].[PersonPhone] pp WHERE pp.PersonID = p.PersonID ORDER BY pp.ModifiedUTC DESC) AS Phone
        -- 按四个判定字段分区，统计同组总记录数
        ,COUNT(1) OVER (
            PARTITION BY pe.[EMailAddress], pn.[FirstName], pn.[LastName], p.[DOB]
        ) AS GroupTotal
    FROM [Rez].[Person] p
    INNER JOIN [Rez].[PersonName] pn ON p.PersonID = pn.PersonID
    INNER JOIN [Rez].[Agent] a ON a.PersonID = p.PersonID
    INNER JOIN [Rez].[PersonEMail] pe ON pe.PersonID = p.PersonID
    INNER JOIN [Rez].[AgentRole] ar ON ar.[AgentID] = a.[AgentID]
    WHERE a.CreatedUTC > '2018-01-01'
)
SELECT 
    CustomerNumber
    ,Title
    ,FirstName
    ,LastName
    ,AgentID
    ,AgentName
    ,PersonID
    ,EMailAddress
    ,Phone
    -- 不需要返回DOB可注释掉下一行
    ,[DOB]
FROM BaseQuery
WHERE GroupTotal > 1 -- 过滤掉无重复的单条记录
ORDER BY EMailAddress, FirstName, LastName, [DOB]

低版本兼容写法（不支持窗口函数时使用） #

如果使用的是SQL Server 2005及更早不支持OVER()窗口函数的版本，可改用EXISTS子查询实现，执行效率略低于窗口函数写法：

SELECT 
    p.CustomerNumber
    ,pn.[Title]
    ,pn.[FirstName]
    ,pn.[LastName]
    ,a.[AgentID]
    ,a.[AgentName]
    ,a.[PersonID]
    ,pe.[EMailAddress]
    ,p.[DOB]
    ,(SELECT TOP 1 pp.[PhoneCode] FROM [Rez].[PersonPhone] pp WHERE pp.PersonID = p.PersonID ORDER BY pp.ModifiedUTC DESC) AS Phone
FROM [Rez].[Person] p
INNER JOIN [Rez].[PersonName] pn ON p.PersonID = pn.PersonID
INNER JOIN [Rez].[Agent] a ON a.PersonID = p.PersonID
INNER JOIN [Rez].[PersonEMail] pe ON pe.PersonID = p.PersonID
INNER JOIN [Rez].[AgentRole] ar ON ar.[AgentID] = a.[AgentID]
WHERE a.CreatedUTC > '2018-01-01'
AND EXISTS (
    SELECT 1
    FROM [Rez].[Person] p2
    INNER JOIN [Rez].[PersonName] pn2 ON p2.PersonID = pn2.PersonID
    INNER JOIN [Rez].[Agent] a2 ON a2.PersonID = p2.PersonID
    INNER JOIN [Rez].[PersonEMail] pe2 ON pe2.PersonID = p2.PersonID
    INNER JOIN [Rez].[AgentRole] ar2 ON ar2.[AgentID] = a2.[AgentID]
    WHERE a2.CreatedUTC > '2018-01-01'
      AND pe2.[EMailAddress] = pe.[EMailAddress]
      AND pn2.[FirstName] = pn.[FirstName]
      AND pn2.[LastName] = pn.[LastName]
      AND p2.[DOB] = p.[DOB]
    GROUP BY pe2.[EMailAddress], pn2.[FirstName], pn2.[LastName], p2.[DOB]
    HAVING COUNT(1) > 1
)
ORDER BY pe.[EMailAddress], pn.[FirstName], pn.[LastName], p.[DOB]

效果验证 #

针对提供的样例数据，ab@d.com/server/test/11/28/2002分组共3条记录，会被全部返回；其余分组仅1条记录，会被自动过滤，和预期返回结果完全一致。

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