仅通过内在加入查询获得某些字段的重复行

Question

如何仅在查询下方获得重复行？

 SELECT p.CustomerNumber
          ,pn.[Title]
          ,pn.[FirstName]
          ,pn.[LastName]
          ,a.[AgentID]
          ,a.[AgentName]
          ,a.[PersonID]
          ,pe.[EMailAddress]
          ,(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'
      ORDER BY pe.[EMailAddress], pn.[FirstName], pn.[LastName], p.[DOB]

在上面的查询中，我想通过pe获得重复记录。

当前数据：

First Name  Last Name   Agent Id    DOB          Email
server      test        2803815     11/28/2002  [email protected]
server      test        2803815     11/28/2002  [email protected]
Test        TC          2803882     11/28/2002  [email protected]
Test        AAA         2804713     11/28/2002  [email protected]
server      test        2803015     11/28/2002  [email protected]

在上面的数据中，我只想获得前2行＆amp;最后一行，因为这些数据是相同的数据，具有相同的名称，lastname，dob＆amp;电子邮件地址

所需的数据：

server      test        2803815     11/28/2002  [email protected]
server      test        2803815     11/28/2002  [email protected]
server      test        2803015     11/28/2002  [email protected]

Answer 1

您可以使用此方法来计数记录，并排除只有一个记录的记录

SELECT * FROM
(

SELECT T1.*, 
COUNT(*) OVER (PARTITION BY 
                T1.CustomerNumber,
                T1.Title,
                T1.FirstName,
                T1.[LastName],
                T1.[AgentID],
                T1.[AgentName],
                T1.[PersonID],
                T1.[EMailAddress]
) As DuplicateRowCount

FROM
     (
      SELECT p.CustomerNumber
          ,pn.[Title]
          ,pn.[FirstName]
          ,pn.[LastName]
          ,a.[AgentID]
          ,a.[AgentName]
          ,a.[PersonID]
          ,pe.[EMailAddress]
          ,(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'
     ) T1
) T2
WHERE T2.DuplicateRowCount > 1
ORDER BY T2.[EMailAddress], T2.[FirstName], T2.[LastName], T2.[DOB]