如何编写SQL查询找出users表中关联重复字段的所有行？

表结构与测试数据 #

创建表的SQL：

DROP TABLE users;
CREATE TABLE users(
id int,
user_id int,
phone_number VARCHAR(30),
email VARCHAR(30));

插入测试数据的SQL：

INSERT INTO users
VALUES
(1, 999, 61412308310, 'can@gmail.com '),
(2, 129, 61477708777, 'acdc@gmail.com '),
(3, 213, 61488908495, 'adel99@gmail.com'),
(4, 145, 61477708777, 'austr@gmail.com'),
(5, 214, 61421445777, 'austr@gmail.com'),
(6, 214, 61421445326, 'jango@gmail.com');

解决方案 #

以下两种写法均可筛选出所有存在重复user_id、phone_number或email的行：

方法一：使用IN子查询 #

SELECT *
FROM users
WHERE user_id IN (SELECT user_id FROM users GROUP BY user_id HAVING COUNT(*) > 1)
   OR phone_number IN (SELECT phone_number FROM users GROUP BY phone_number HAVING COUNT(*) > 1)
   OR email IN (SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1);

方法二：使用EXISTS（大表场景性能更优） #

SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1 FROM users u2 WHERE u2.user_id = u.user_id AND u2.id != u.id)
   OR EXISTS (SELECT 1 FROM users u2 WHERE u2.phone_number = u.phone_number AND u2.id != u.id)
   OR EXISTS (SELECT 1 FROM users u2 WHERE u2.email = u.email AND u2.id != u.id);

结果说明 #

两种写法都会返回预期的4条记录：

2, 129, 61477708777, 'acdc@gmail.com '
4, 145, 61477708777, 'austr@gmail.com'
5, 214, 61421445777, 'austr@gmail.com'
6, 214, 61421445326, 'jango@gmail.com'

• 方法一通过子查询先找出各字段中重复的值集合，再匹配原表数据；
• 方法二通过EXISTS直接校验当前行是否存在同字段值的其他记录，避免分组操作带来的性能损耗。

内容的提问来源于stack exchange，提问作者wdad asd