MySQL实现教师关联手机号记录单行合并的方法

你需要将每个教师的多条手机号记录转换为一行多列的格式，这属于行转列操作，在MySQL中可以通过两种方案实现：

方案一：MySQL 8.0及以上版本（支持窗口函数） #

利用ROW_NUMBER()窗口函数为每个教师的手机号分配序号，再通过条件聚合将不同序号的手机号映射到不同列：

SELECT 
    ID_TEACHER,
    MAX(CASE WHEN rn = 1 THEN PHONE END) AS PHONE1,
    MAX(CASE WHEN rn = 1 THEN NUMBER END) AS NUMBER1,
    MAX(CASE WHEN rn = 2 THEN PHONE END) AS PHONE2,
    MAX(CASE WHEN rn = 2 THEN NUMBER END) AS NUMBER2,
    MAX(CASE WHEN rn = 3 THEN PHONE END) AS PHONE3,
    MAX(CASE WHEN rn = 3 THEN NUMBER END) AS NUMBER3
FROM (
    -- 子查询：为每个教师的手机号分配序号
    SELECT 
        T.ID_TEACHER,
        P.PHONE,
        P.NUMBER,
        ROW_NUMBER() OVER (PARTITION BY T.ID_TEACHER ORDER BY P.PHONE) AS rn
    FROM TEACHER T 
    LEFT JOIN PHONES P ON P.IDPERSON = T.ID_TEACHER
) AS temp
GROUP BY ID_TEACHER;

说明： #

• ROW_NUMBER() OVER (PARTITION BY T.ID_TEACHER ORDER BY P.PHONE)：按教师ID分组，为每组内的手机号按PHONE字段排序并生成1、2、3...的序号。
• 外层的MAX(CASE ...)：针对每个序号提取对应的手机号和号码，没有对应数据时返回NULL（对应你期望的空值）。
• 如果教师手机号数量超过3个，只需继续增加rn=4、rn=5等对应的CASE分支即可。

方案二：MySQL 8.0以下版本（不支持窗口函数） #

使用用户变量手动生成序号，再通过条件聚合实现转列：

SELECT 
    ID_TEACHER,
    MAX(CASE WHEN rn = 1 THEN PHONE END) AS PHONE1,
    MAX(CASE WHEN rn = 1 THEN NUMBER END) AS NUMBER1,
    MAX(CASE WHEN rn = 2 THEN PHONE END) AS PHONE2,
    MAX(CASE WHEN rn = 2 THEN NUMBER END) AS NUMBER2,
    MAX(CASE WHEN rn = 3 THEN PHONE END) AS PHONE3,
    MAX(CASE WHEN rn = 3 THEN NUMBER END) AS NUMBER3
FROM (
    -- 子查询：用用户变量生成每个教师的手机号序号
    SELECT 
        T.ID_TEACHER,
        P.PHONE,
        P.NUMBER,
        @rn := IF(@prev_id = T.ID_TEACHER, @rn + 1, 1) AS rn,
        @prev_id := T.ID_TEACHER
    FROM TEACHER T 
    LEFT JOIN PHONES P ON P.IDPERSON = T.ID_TEACHER,
    (SELECT @prev_id := NULL, @rn := 0) AS vars
    ORDER BY T.ID_TEACHER, P.PHONE
) AS temp
GROUP BY ID_TEACHER;

说明： #

• @prev_id和@rn是用户变量，分别用来记录上一条数据的教师ID和当前序号。
• 当教师ID与上一条相同时，序号自增；否则重置为1。
• 同样，若手机号数量超过3个，可扩展对应的CASE分支。

注意事项 #

SQL中不允许同一查询结果出现重复列名，因此示例中用PHONE1、NUMBER1这类带序号的别名，你可以根据需求调整别名（比如PHONE_1、PHONE_2），最终展示时可自行修改显示名称。

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