您好,我想找出哪些客户有相同的电子邮件,但有一个不同的名字。在我们的名字,名字,姓氏中发生了很大的变化,它填充了电子邮件的名字,所以它看起来就像name at-yahoo。所以我找到了所有的-at- now,我想把这些电子邮件和无名氏的正确名字匹配起来,这样我就可以修复所有的-at- email。希望所有有意义的人都能帮上忙。
SELECT C.CUST_NO, C.NAM, C.EMAIL_ADRS_1
FROM AR_CUST C
INNER JOIN (SELECT NAM, EMAIL_ADRS_1, COUNT(*) AS COUNTOF
FROM AR_CUST
GROUP BY NAM, EMAIL_ADRS_1
HAVING COUNT(*)>1
) DT ON C.NAM = DT.NAM AND C.EMAIL_ADRS_1 = DT.EMAIL_ADRS_1
ORDER BY C.EMAIL_ADRS_1
此代码将仅显示所有重复的电子邮件
SELECT C.CUST_NO, C.NAM, C.FST_NAM, C.LST_NAM, C.EMAIL_ADRS_1
FROM AR_CUST C
INNER JOIN (SELECT NAM, EMAIL_ADRS_1, COUNT(*) AS COUNTOF
FROM AR_CUST
GROUP BY NAM, EMAIL_ADRS_1
HAVING COUNT(*)>1 AND NAM LIKE '%-at-%'
) DT ON C.EMAIL_ADRS_1 = DT.EMAIL_ADRS_1
ORDER BY C.EMAIL_ADRS_1
这段代码将给我带来- at - email和一些相关的名字,但它只给我带来100条记录,而实际上应该至少有900条记录
发布于 2019-06-13 03:45:32
你好,我正在找哪些客户有相同的电子邮件,但有一个不同的名字。
如果你只是想要重复的电子邮件地址,你可以用聚合来回答这个问题:
SELECT C.EMAIL_ADRS_1
FROM AR_CUST C
GROUP BY C.EMAIL_ADRS_1
HAVING MIN(C.NAM) <> MAX(C.NAM);
如果需要原始记录,请使用exists
select c.*
from ar_cust c
where exists (select 1
from ar_cust c2
where c2.email_adrs_1 = c.email_adrs_1 and
c2.nam <> c.nam
);
https://stackoverflow.com/questions/56567901
复制相似问题