WITH base AS (
SELECT DISTINCT u.UserId
        ,u.acitveid
        ,cd.email
        ,u.userstatusid
        ,
    FROM ContactDetails cd
    JOIN UserContactDetails ucd ON ucd.ContactDetailsId = cd.ContactDetailsId
    JOIN dbo.[User] u ON u.UserId = ucd.UserId
)
,duplicate_emails AS (
    SELECT email
        ,count(userId) AS cnt
    FROM base
    GROUP BY 1
    HAVING count(userId) > 1
)
SELECT b.*
FROM base b
JOIN duplicate_emails de ON b.email = de.email

在Email = email和id〈〉id之间的self join在这里可以很好地工作。也就是说，您的请求和lack of sample data意味着我们在很大程度上是根据您提供的查询和示例输出进行猜测。下面的内容应该会让您非常接近，如果您更新OP，我相信我们可以准确地为您提供您所需的内容。

SELECT ActiveUser.UserID Active_UserID,
    ActiveUser.ActiveID Active_ActiveID,
    ContactDetails.email AS Email,
    DuplicateUser.UserID AS Dup_UserID,
    DuplicateUser.ActiveID As Dup_ActiveID
FROM ContactDetails INNER JOIN 
    ContactDetails AS Duplicate ON ContactDetails.email = Duplicate.email AND ContactDetails.UserID <> Duplicate.UserID INNER JOIN
    UserContactDetails AS ActiveUserContactDetails ON ActiveUserContactDetails.ContactDetailsID = ContactDetails.ContactDetailsID INNER JOIN
    dbo.[User] AS ActiveUser ON ActiveUser.UserID = ActiveUserContactDetails.UserID INNER JOIN
    UserContactDetails AS DuplicateUserContactDetails ON DuplicateUserContactDetails.ContactDetailsID = Duplicate.ContactDetailsID INNER JOIN
    dbo.[User] AS DuplicateUser ON DuplicateUser.UserID = UserContactDetails.UserID