SQL如何实现同列不同类型值拆分至独立列并按用户聚合

核心逻辑是先识别communication列里的内容类型，区分邮箱和手机号，再按用户分组把不同类型的联系方式映射到对应列，多个手机号按记录顺序依次分配到PHONE1、PHONE2字段。

前置判断规则 #

• 字段值包含@符号的判定为邮箱，存入EMAIL列
• 其余纯数字内容判定为手机号，按同用户下的记录ID从小到大排序，依次存入PHONE1、PHONE2列
• 同用户输出行的ID取该用户所有记录中的最小ID，和示例输出规则一致

可直接使用的查询语句 #

以下是MySQL 8.0及以上版本（支持窗口函数）的写法，把代码里的your_table替换成你实际的表名即可：

WITH contact_mark AS (
    SELECT
        MIN(ID) OVER(PARTITION BY `USER`) AS out_id,
        `USER`,
        communication,
        -- 给不同类型的联系方式打标，手机号按顺序编号
        CASE
            WHEN communication LIKE '%@%' THEN 'EMAIL'
            ELSE CONCAT('PHONE', ROW_NUMBER() OVER(
                PARTITION BY `USER`, CASE WHEN communication LIKE '%@%' THEN 0 ELSE 1 END
                ORDER BY ID
            ))
        END AS field_tag
    FROM your_table
)
SELECT
    out_id AS ID,
    `USER`,
    MAX(CASE WHEN field_tag = 'EMAIL' THEN communication END) AS EMAIL,
    MAX(CASE WHEN field_tag = 'PHONE1' THEN communication END) AS PHONE1,
    MAX(CASE WHEN field_tag = 'PHONE2' THEN communication END) AS PHONE2
FROM contact_mark
GROUP BY out_id, `USER`;

适配调整说明 #

• 如果你用的是不支持窗口函数的低版本数据库，可以通过自定义用户变量的方式给手机号生成序号，核心分组打标的逻辑不变
• 如果单个用户的手机号超过2个，参照PHONE2的写法新增对应判断行即可，比如要支持3个手机号就新增MAX(CASE WHEN field_tag = 'PHONE3' THEN communication END) AS PHONE3
• 如果你的手机号存储格式包含特殊字符（比如+86前缀、中间带-分隔符），调整CASE语句里的手机号判断规则即可

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