MySQL多表LEFT JOIN结果动态PIVOT行转列实现方案

需求梳理 #

• 基础关联逻辑：对users、items、items_additional、items_ask_user四张表执行三次左关联
• 原始查询问题：同一用户关联多条附加项、提问记录时会返回多行明细，返回字段包含ID、item_id、name、surname、addition、question、amount
• 目标输出规则：
  • 按用户维度聚合为宽表
  • 同一用户的多条addition值横向展开为addition、addition2、addition3……列
  • 同一用户的多条question值横向展开为question1、question2、question3……列
  • 空值统一填充为-
  • 同一用户的amount字段求和聚合
• 场景限制：不同用户对应的addition、question数量不固定，列数需动态生成，不支持硬编码

原有代码问题 #

• 序号生成逻辑错误：计算动态列序号时未对附加项、提问分别计数，且存在表名混用问题（同时出现items、event_items两套表名）
• 边界场景缺失：未处理无附加项、无提问时的空值、SQL语法错误问题
• CTE语法不完整：CTE定义缺少闭合括号
• 分组逻辑错误：分组字段未包含关联主键，易出现聚合结果偏差
• 空值逻辑缺失：未实现空值填充为-的要求

可落地实现代码 #

-- 初始化变量
SET @add_col_sql = NULL;
SET @qst_col_sql = NULL;
SET @max_add_cnt = NULL;
SET @max_qst_cnt = NULL;

-- 计算全量用户中最多的附加项数、提问数，确定动态列总数量
SELECT 
  MAX(add_cnt),
  MAX(qst_cnt) 
INTO @max_add_cnt, @max_qst_cnt
FROM (
  SELECT 
    users.id,
    COUNT(DISTINCT items_additional.id) AS add_cnt,
    COUNT(DISTINCT items_ask_user.id) AS qst_cnt
  FROM users
  LEFT JOIN items on users.id = items.id
  LEFT JOIN items_additional on items.id = items_additional.items_id
  LEFT JOIN items_ask_user on items.id = items_ask_user.items_id
  GROUP BY users.id
) t;

-- 生成附加项动态列SQL
WITH RECURSIVE add_seq AS (
  SELECT 1 AS seq
  UNION ALL
  SELECT seq + 1 FROM add_seq WHERE seq < @max_add_cnt
)
SELECT GROUP_CONCAT(
  CONCAT('COALESCE(MAX(CASE WHEN rn_add = ', seq, ' THEN addition END), ''-'') AS addition', IF(seq=1,'',seq))
ORDER BY seq
) INTO @add_col_sql
FROM add_seq;

-- 生成提问动态列SQL
WITH RECURSIVE qst_seq AS (
  SELECT 1 AS seq
  UNION ALL
  SELECT seq + 1 FROM qst_seq WHERE seq < @max_qst_cnt
)
SELECT GROUP_CONCAT(
  CONCAT('COALESCE(MAX(CASE WHEN rn_qst = ', seq, ' THEN question END), ''-'') AS question', seq)
ORDER BY seq
) INTO @qst_col_sql
FROM qst_seq;

-- 处理无附加项/无提问的边界场景
SET @add_col_sql = IF(@max_add_cnt IS NULL OR @max_add_cnt = 0, "'-' AS addition", @add_col_sql);
SET @qst_col_sql = IF(@max_qst_cnt IS NULL OR @max_qst_cnt = 0, "'-' AS question1", @qst_col_sql);

-- 拼接最终查询SQL
SET @final_sql = CONCAT(
'WITH base_rn AS (
  SELECT 
    users.id AS user_id,
    items.id AS item_id,
    users.name,
    users.surname,
    items_additional.addition,
    items_ask_user.question,
    items_ask_user.amount,
    ROW_NUMBER() OVER(PARTITION BY users.id ORDER BY items_additional.id) AS rn_add,
    ROW_NUMBER() OVER(PARTITION BY users.id ORDER BY items_ask_user.id) AS rn_qst
  FROM users
  LEFT JOIN items on users.id = items.id
  LEFT JOIN items_additional on items.id = items_additional.items_id
  LEFT JOIN items_ask_user on items.id = items_ask_user.items_id
)
SELECT 
  user_id,
  item_id,
  name,
  surname,
  ', @add_col_sql, ',
  ', @qst_col_sql, ',
  SUM(amount) AS total_amount
FROM base_rn
GROUP BY user_id, item_id, name, surname'
);

-- 执行动态SQL
PREPARE stmt FROM @final_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

逻辑说明 #

• 用递归CTE生成连续序号，确保动态列数和全量用户最大的附加项、提问数匹配，不会出现列数不足导致的数据截断
• 附加项、提问分别生成独立行号，两类数据的展开逻辑互不干扰
• 用COALESCE函数统一将聚合后的空值替换为-，符合输出要求
• 聚合采用MAX(CASE...)结构实现行转列，SUM函数对amount字段求和，分组字段包含业务主键，避免聚合错误
• 增加边界场景判断，当全量用户无附加项/无提问时，SQL依然可以正常执行不会报语法错误

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