MySQL动态列数透视表生成：用户多邮箱行转列查询需求

嘿，这个需求我之前做项目时刚好碰到过！因为邮箱列的数量不固定，静态SQL肯定满足不了，得靠动态SQL来实现。我给你一步步拆解怎么做：

核心思路 #

首先得统计每个用户最多有多少个邮箱，以此确定要生成多少列；然后给每个用户的邮箱按顺序排名，最后通过动态拼接SQL把不同排名的邮箱映射到对应的列上。

步骤1：先确认最大邮箱数量（可选，但能帮你理解数据） #

先跑这条SQL看看你的数据里单个用户最多有多少个邮箱，心里有数：

SELECT MAX(email_count) AS max_emails
FROM (
    SELECT user_id, COUNT(*) AS email_count
    FROM your_table_name -- 替换成你的实际表名
    GROUP BY user_id
) AS user_email_counts;

步骤2：用动态SQL生成透视表 #

因为要动态生成列，我们可以写一个存储过程来自动处理。下面的代码兼容MySQL 8.0+（支持窗口函数），如果是低版本我后面会给兼容方案：

DELIMITER //
CREATE PROCEDURE GenerateEmailPivot()
BEGIN
    DECLARE max_cols INT;
    DECLARE col_sql VARCHAR(1000);
    DECLARE pivot_sql VARCHAR(2000);

    -- 获取单个用户的最大邮箱数
    SELECT MAX(email_count) INTO max_cols
    FROM (
        SELECT user_id, COUNT(*) AS email_count
        FROM your_table_name -- 替换成你的实际表名
        GROUP BY user_id
    ) AS counts;

    -- 动态生成列的SQL片段：比如primary_email, secondary_email_1, secondary_email_2...
    SET col_sql = '';
    SET @i = 1;
    WHILE @i <= max_cols DO
        SET col_sql = CONCAT(col_sql, 
            ', MAX(CASE WHEN email_rank = ', @i, ' THEN email END) AS ', 
            -- 第一列叫primary_email，后面的按secondary_email_1、2命名，可按需修改
            IF(@i=1, 'primary_email', CONCAT('secondary_email_', @i-1))
        );
        SET @i = @i + 1;
    END WHILE;

    -- 拼接完整的透视查询SQL
    SET pivot_sql = CONCAT(
        'SELECT user_id ', col_sql, ' 
        FROM (
            -- 给每个用户的邮箱按顺序排名
            SELECT user_id, email,
                ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY email) AS email_rank
            FROM your_table_name -- 替换成你的实际表名
        ) AS ranked_emails
        GROUP BY user_id;'
    );

    -- 执行动态生成的SQL
    PREPARE stmt FROM pivot_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

调用方式

创建好存储过程后，直接执行这条命令就能得到你要的透视表：

CALL GenerateEmailPivot();

兼容MySQL 8.0以下版本（无窗口函数） #

如果你的MySQL版本低于8.0，没有ROW_NUMBER()窗口函数，可以用用户变量来实现邮箱排名，把上面存储过程里的子查询替换成下面这段：

SELECT 
    user_id, 
    email,
    @rank := IF(@current_user = user_id, @rank + 1, 1) AS email_rank,
    @current_user := user_id
FROM your_table_name, (SELECT @current_user := NULL, @rank := 0) AS init
ORDER BY user_id, email;

额外优化点 #

如果你的邮箱有明确的主/次规则（比如包含primary的是主邮箱），可以调整排名时的ORDER BY条件，确保主邮箱对应primary_email列：

-- 把排名部分的ORDER BY改成这样
ORDER BY 
    CASE WHEN email LIKE '%primary%' THEN 1 ELSE 2 END, -- 主邮箱排前面
    email;

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