使用SQL合并重复行：基于手机号整合格式重复的用户名

处理前的数据表示例 #

样例数据：

用户名	手机号	附加信息
Mr. John	138xxxx1234	会员等级A
John Mr.	138xxxx1234	积分1000
Ms. Alice	139xxxx5678	会员等级B
Alice Ms.	139xxxx5678	积分2000

处理后的数据表示例 #

样例数据：

标准用户名	手机号	合并附加信息
Mr. John	138xxxx1234	会员等级A, 积分1000
Ms. Alice	139xxxx5678	会员等级B, 积分2000

SQL实现方案 #

核心逻辑：用手机号作为唯一分组依据，按手机号聚合，选择一个统一的用户名格式，同时合并同手机号下的其他字段内容。

1. MySQL 版本 #

快速生成去重新表（取最早出现的用户名）

CREATE TABLE deduplicated_users AS
SELECT
    -- 取该手机号下第一条出现的用户名作为标准，也可换成MAX()取最后出现的
    MIN(username) AS standard_username,
    phone_number,
    -- 用逗号合并所有附加信息，DISTINCT避免重复内容
    GROUP_CONCAT(DISTINCT extra_info SEPARATOR ', ') AS merged_info
FROM original_users
GROUP BY phone_number;

优先保留带前缀的用户名（比如Mr./Ms.）

如果想优先用Mr. XX或Ms. XX这种格式的用户名作为标准，可调整逻辑：

CREATE TABLE deduplicated_users AS
SELECT
    CASE
        -- 先检查有没有带前缀的用户名，有就取它
        WHEN COUNT(CASE WHEN username LIKE 'Mr.%' OR username LIKE 'Ms.%' THEN 1 END) > 0
            THEN MAX(CASE WHEN username LIKE 'Mr.%' OR username LIKE 'Ms.%' THEN username END)
        -- 没有的话就取最早出现的
        ELSE MIN(username)
    END AS standard_username,
    phone_number,
    GROUP_CONCAT(DISTINCT extra_info SEPARATOR ', ') AS merged_info
FROM original_users
GROUP BY phone_number;

2. PostgreSQL 版本 #

快速生成去重新表

CREATE TABLE deduplicated_users AS
SELECT
    MIN(username) AS standard_username,
    phone_number,
    -- PostgreSQL用STRING_AGG合并字符串
    STRING_AGG(DISTINCT extra_info, ', ') AS merged_info
FROM original_users
GROUP BY phone_number;

优先保留带前缀的用户名

CREATE TABLE deduplicated_users AS
SELECT
    -- COALESCE优先取第一个非空值
    COALESCE(
        MAX(CASE WHEN username LIKE 'Mr.%' OR username LIKE 'Ms.%' THEN username END),
        MIN(username)
    ) AS standard_username,
    phone_number,
    STRING_AGG(DISTINCT extra_info, ', ') AS merged_info
FROM original_users
GROUP BY phone_number;

注意事项 #

• 如果其他字段是数值类型（比如积分），别用字符串合并，改用SUM()、MAX()这类聚合函数。
• 可以根据业务需求调整用户名的选择逻辑，比如按记录创建时间最新的用户名作为标准。
• 执行建表语句前，建议先跑一遍SELECT语句验证结果，避免直接生成错误的新表。

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