如何编写MySQL查询语句实现按group列分组日志表并生成自定义列

没问题，这就给你搞定这个MySQL查询需求！针对你的log表，我们可以分两种核心场景来实现：一是按group分组合并同组的JSON键值对，二是把这些JSON的键单独拆成列展示。下面是具体的实现方案，分版本说明兼容性：

一、按group分组合并成单个JSON对象 #

方案A：MySQL 8.0+版本（推荐，支持多键JSON） #

MySQL 8.0引入的JSON_TABLE函数可以轻松拆分JSON的键值对，再结合JSON_OBJECTAGG聚合函数就能合并成完整的JSON对象：

SELECT 
  `group`,
  JSON_OBJECTAGG(key_name, value) AS merged_response
FROM (
  -- 先拆分每个response的键和对应值
  SELECT 
    l.`group`,
    jk.key_name,
    JSON_EXTRACT(l.response, CONCAT('$.', jk.key_name)) AS value
  FROM log l
  JOIN JSON_TABLE(
    JSON_KEYS(l.response),
    '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
  ) jk
) AS extracted_keys_values
GROUP BY `group`;

这个查询会先把每条记录的response拆成key_name和value的行，再按group聚合，用JSON_OBJECTAGG把同组的键值对合并成一个JSON对象，正好符合你示例里的预期结果。

方案B：MySQL 5.7版本（兼容旧版，适合单键JSON） #

如果你的MySQL版本是5.7（不支持JSON_TABLE），而你的每条response只有一个JSON键（就像示例数据那样），可以用GROUP_CONCAT拼接键值对再转成JSON：

SELECT 
  `group`,
  JSON_UNQUOTE(
    CONCAT('{', GROUP_CONCAT(
      CONCAT('"', JSON_UNQUOTE(JSON_KEYS(response)), '":', response)
      SEPARATOR ','
    ), '}')
  ) AS merged_response
FROM log
GROUP BY `group`;

注意：这个方法只适合每条response只有一个键的情况，如果有多个键会导致JSON格式错误。

二、将JSON键作为单独列展示 #

场景1：已知所有JSON键（静态列） #

如果你已经明确知道所有可能出现的JSON键（比如last_name、first_name、dob），可以直接用JSON_EXTRACT提取，结合MAX（因为同组每个键只会出现一次）来聚合：

SELECT 
  `group`,
  MAX(JSON_UNQUOTE(JSON_EXTRACT(response, '$.last_name'))) AS last_name,
  MAX(JSON_UNQUOTE(JSON_EXTRACT(response, '$.first_name'))) AS first_name,
  MAX(JSON_UNQUOTE(JSON_EXTRACT(response, '$.dob'))) AS dob
FROM log
GROUP BY `group`;

场景2：未知所有JSON键（动态生成列） #

如果JSON键是不固定的，需要自动识别所有出现过的键并生成列，可以用存储过程实现动态SQL：

DELIMITER //
CREATE PROCEDURE get_pivoted_log_data()
BEGIN
  DECLARE dynamic_columns TEXT;
  
  -- 自动提取所有唯一的JSON键，生成对应的列表达式
  SELECT GROUP_CONCAT(DISTINCT 
    CONCAT('MAX(JSON_UNQUOTE(JSON_EXTRACT(response, ''$.', key_name, '''))) AS ', key_name)
  ) INTO dynamic_columns
  FROM (
    SELECT JSON_UNQUOTE(JSON_KEYS(response)) AS key_name
    FROM log
  ) AS all_unique_keys;
  
  -- 拼接并执行动态SQL
  SET @sql = CONCAT('SELECT `group`, ', dynamic_columns, ' FROM log GROUP BY `group`');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL get_pivoted_log_data();

这个存储过程会自动扫描所有response里的键，动态生成包含所有键的查询，无需手动维护列名。

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