MySQL 5.7按email_id行转列查询及报错解决求助

现有MySQL 5.7表submissions，结构及数据如下：

| email_id | source | message_id | submit_dt |
----------------------------------------------
|   id100  | google |   msg227   | 2024-11-12|
|   id200  | yahoo  |   msg227   | 2024-11-12|
|   id300  | google |   msg227   | 2024-11-12|
|   id100  | yahoo  |   msg227   | 2024-11-11|
|   id200  | google |   msg227   | 2024-11-11|
|   id300  | aol    |   msg227   | 2024-11-10|

需要将数据转换为以下格式：每个email_id对应一行，横向展示其关联的多组submit_dt、source、message_id信息：

| email_id | submit_dt | source | message_id | submit_dt | source | message_id | repeat
-------------------------------------------------------------------------------------------
| id100    | 2024-11-12| google |   msg227   | 2024-11-11| yahoo  |   msg227   | 
-------------------------------------------------------------------------------------------
| id200    | 2024-11-12| yahoo  |   msg227   | 2024-11-11| google |   msg227   |
-------------------------------------------------------------------------------------------
| id300    | 2024-11-12| google |   msg227   | 2024-11-10| aol    |   msg227   |
-------------------------------------------------------------------------------------------

尝试的SQL语句及报错：

SELECT 
  GROUP_CONCAT(`email_id`) AS 'Email ID'
  , `submit_dt`
  , `source` 
  , `message_id`
FROM `submissions` 
WHERE `message_id` LIKE '240227' GROUP BY `email_id` ORDER BY `submissions`.`email_id` ASC;

报错信息：

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'righters_db.submissions.submit_dt' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

你的原SQL无法实现需求的核心原因：

1. GROUP BY email_id后，submit_dt、source等非聚合列未被聚合，也不在GROUP BY列表中，违反了only_full_group_by模式的约束（MySQL要求GROUP BY后的SELECT列必须是GROUP BY列或被聚合函数包裹）；
2. GROUP_CONCAT(email_id)完全多余，因为GROUP BY email_id后每个分组的email_id是唯一的。

针对MySQL 5.7（不支持窗口函数），可以通过变量编号+条件聚合的方式实现行转列，具体SQL如下：

SELECT
    email_id,
    MAX(CASE WHEN rn = 1 THEN submit_dt END) AS submit_dt,
    MAX(CASE WHEN rn = 1 THEN source END) AS source,
    MAX(CASE WHEN rn = 1 THEN message_id END) AS message_id,
    MAX(CASE WHEN rn = 2 THEN submit_dt END) AS submit_dt,
    MAX(CASE WHEN rn = 2 THEN source END) AS source,
    MAX(CASE WHEN rn = 2 THEN message_id END) AS message_id,
    '' AS `repeat`
FROM (
    SELECT
        email_id,
        source,
        message_id,
        submit_dt,
        -- 给每个email_id的记录按submit_dt降序编号，最新的为1，次新的为2
        @rn := IF(@prev_email = email_id, @rn + 1, 1) AS rn,
        @prev_email := email_id
    FROM submissions
    -- 初始化变量
    CROSS JOIN (SELECT @prev_email := '', @rn := 0) AS vars
    WHERE message_id = 'msg227' -- 注意匹配实际message_id值，原SQL的LIKE '240227'与示例数据不符
    ORDER BY email_id, submit_dt DESC
) AS ranked_records
GROUP BY email_id
ORDER BY email_id;

逻辑说明： #

1. 子查询ranked_records：使用用户变量@rn为每个email_id的记录按提交时间倒序编号，确保最新的记录排在第1位；
2. 外层查询：通过CASE WHEN配合MAX聚合函数，将编号为1和2的记录字段分别提取并横向拼接，实现一行展示多个提交记录的效果；
3. 如果某个email_id只有1条记录，对应的第二组字段会显示为NULL，可以根据需求用COALESCE替换为空字符串。

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