mysql - 检查 MySQL 数据库中的多个重复项

Question

我目前有 3 个不同的查询来检查数据库中的多个用户，我想知道是否有一种方法可以将所有这些都组合在一个查询中。

基于电子邮件：

SELECT 
    user_email,
    COUNT(user_email)
FROM
    users
GROUP BY user_email
HAVING COUNT(user_email) > 1;

根据姓氏：

SELECT 
    user_lastname,
    COUNT(user_lastname)
FROM
    users
GROUP BY user_lastname
HAVING COUNT(user_lastname) > 1;

基于电话：

SELECT 
    user_phone,
    COUNT(user_phone)
FROM
    users
GROUP BY user_phone
HAVING COUNT(user_phone) > 1;

对于每个查询，我都会显示一个电子邮件列表|姓氏|电话号码和多个用户的数量。我的最终计划是列出一个仅包含重复用户的列表，其中仅包含他们捕获的信息（例如基于 U 电子邮件等的用户 X / Y 重复）。

User_ID | user_email     | user_firstname | user_lastname | user_phone
1       | [e-mail]   | John           | Snow          | 123456
2       | [e-mail]   | George         | Smith         | 546632
3       | [e-mail]    | Maria          | Coal          | 553211
4       | [e-mail]   | Jack           | Black         | 752210
5       | [e-mail]    | Tom            | Bing          | 856332
6       | [e-mail]    | Storm          | Snow          | 325412
7       | [e-mail]   | Mairy          | Call          | 123456
8       | [e-mail] | Kim            | Loren         | 351200
9       | [e-mail] | Dot            | Honey         | 546632
10      | [e-mail] | Jack           | Smithson      | 455871

最终结果必须显示用户 1+4（相同的电子邮件）+7（相同的电话号码和 1）+6（相同的姓氏和 1）+2+9（相同的电话号码）

User_ID | user_email     | user_firstname | user_lastname | user_phone
1       | [e-mail]   | John           | Snow          | 123456
4       | [e-mail]   | Jack           | Black         | 752210
6       | [e-mail]    | Storm          | Snow          | 325412
7       | [e-mail]   | Mairy          | Call          | 123456
2       | [e-mail]   | George         | Smith         | 546632
9       | [e-mail] | Dot            | Honey         | 546632

标签： mysql

Answer 1

对于这种情况，我认为 EXISTS 是最好的解决方案：

select u.* from users u
where exists (
  select 1 from users
  where user_id <> u.user_id
  and (user_lastname = u.user_lastname or user_email = u.user_email or user_phone = u.user_phone)
)  

请参阅演示。
结果：

| User_ID | user_email     | user_firstname | user_lastname | user_phone |
| ------- | -------------- | -------------- | ------------- | ---------- |
| 1       | [e-mail]   | John           | Snow          | 123456     |
| 2       | [e-mail]   | George         | Smith         | 546632     |
| 4       | [e-mail]   | Jack           | Black         | 752210     |
| 6       | [e-mail]    | Storm          | Snow          | 325412     |
| 7       | [e-mail]   | Mairy          | Call          | 123456     |
| 9       | [e-mail] | Dot            | Honey         | 546632     |