mysql - 修改查询以显示所有电子邮件重复项

Question

有2个表

Table 1 : email_addresses 
Structure

id | email_address

1  | shhabji@gmail.com
2  | xyz@gmail.com
3  | abc@gmail.com

Table 2 :   email_addr_bean_rel
Structure
id | email_address_id|bean_module|bean_id|deleted

1  |   1  |  Contacts  | 1 |0
2  |   1  |  Contacts  | 1 |0
3  |   2  |  Contacts  | 2 |0

我希望查询按 email_address 而不是特定的 email_address 显示所有重复项

目前我的查询是

SELECT ear.bean_id FROM email_addresses ea 
    LEFT JOIN email_addr_bean_rel ear ON ea.id = ear.email_address_id 
    WHERE ear.bean_module = 'Contacts' AND 
    ear.bean_id != 'ed87a55d-21fb-a459-276c-533a32df6f36' 
    AND ear.deleted = 0 AND ea.email_address LIKE '%shaabji@gmail.com%'

Answer 1

SELECT email_address, count(email_address) as 'Number_Of_Email_Address' FROM email_addresses
GROUP BY email_address HAVING count(email_address) > 1;

此查询将为您获取表 1 中的重复电子邮件地址。