SQL Server 返回重复的电子邮件沿着不同的用户ID

vatpfxk5  于 2023-01-29  发布在  其他
关注(0)|答案(2)|浏览(127)

我遇到了一个重复查询的问题,我希望你们能帮助。
本质上,我想做的是找到并列出与不同用户ID关联的重复电子邮件
我的疑问是:

select UserId, acitveid, email, userstatusid
from (select  u.UserId, u.acitveid, cd.email, u.userstatusid,
            count(*)over (partition by cd.email) as cnt
            from ContactDetails cd 
            join  UserContactDetails ucd on ucd.ContactDetailsId = cd.ContactDetailsId
            join  dbo.[User] u on u.UserId = ucd.UserId ) ua
    where cnt >1

我在上面的查询中遇到的问题是,它为一些结果返回相同的用户ID,因此看起来像:

Userid   AcitveId      email          UserStatusid
123      1             abc@123.com     1
123      1             abc@123.com     1
135      1             efg@123.com     1
142      1             efg@123.com     1

我期待的结果很简单:

Userid   AcitveId      email          UserStatusid
135      1             efg@123.com     1
142      1             efg@123.com     1
zwghvu4y

zwghvu4y1#

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
mu0hgdu0

mu0hgdu02#

在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

相关问题