PostgreSQL筛选存在重复手机号的重复账号并保留指定行

针对你的需求，我整理了一个PostgreSQL的解决方案，先看完整的实现代码，再一步步拆解思路：

1. 示例数据准备 #

首先我们先创建示例表并插入数据，还原你的测试场景：

CREATE TABLE test_data (
    uuid INT,
    email VARCHAR(50),
    phone_number VARCHAR(10)
);

INSERT INTO test_data VALUES
(1, 'a@gmail.com', '111'),
(2, 'a@gmail.com', '111'),
(3, 'a@gmail.com', '112'),
(4, 'b@gmail.com', '222'),
(5, 'b@gmail.com', '222'),
(6, 'c@gmail.com', '333'),
(7, 'd@gmail.com', '444'),
(8, 'd@gmail.com', '445'),
(9, 'd@gmail.com', '446');

2. 解决方案SQL #

WITH email_phone_stats AS (
    SELECT
        email,
        phone_number,
        -- 统计当前邮箱的总记录数，判断是否为重复邮箱
        COUNT(*) OVER (PARTITION BY email) AS total_email_rows,
        -- 统计当前邮箱下该手机号的出现次数
        COUNT(*) OVER (PARTITION BY email, phone_number) AS phone_count
    FROM test_data
),
valid_candidates AS (
    SELECT
        email,
        phone_number
    FROM email_phone_stats
    -- 排除非重复邮箱（总记录数=1的）
    WHERE total_email_rows > 1
    -- 只保留邮箱下出现过至少2次的手机号（排除所有手机号都唯一的情况）
    AND phone_count >= 2
)
-- 每个邮箱仅保留一行结果
SELECT DISTINCT email, phone_number
FROM valid_candidates;

3. 思路拆解 #

• 第一步：统计关键指标
  用email_phone_stats这个CTE计算两个核心统计值：

  • total_email_rows：通过窗口函数按邮箱分组，得到每个邮箱的总记录数，用来筛选掉仅出现一次的邮箱（比如c@gmail.com）
  • phone_count：按邮箱+手机号分组统计次数，用来识别哪些手机号在同一邮箱下重复出现

• 第二步：过滤有效候选
  valid_candidates CTE只保留满足两个条件的记录：

  1. 邮箱是重复的（total_email_rows > 1）
  2. 该手机号在邮箱下出现至少2次（phone_count >=2），这一步会排除掉像d@gmail.com这种所有手机号都唯一的情况

• 第三步：去重输出结果
  最后用DISTINCT确保每个邮箱只返回一行对应的手机号，得到你期望的结果。

4. 运行结果 #

执行上述SQL后，会得到如下结果：

email           | phone_number
----------------|-------------
a@gmail.com     | 111
b@gmail.com     | 222

内容的提问来源于stack exchange，提问作者Flavio de Falcao e Helena