MySQL中如何将给定示例数据转置为指定结构的结果表

你需要实现的是宽表转窄表的列转行（逆透视）操作，根据使用的MySQL版本不同，有两种常用实现方案，最终输出都可以匹配目标表结构要求。

方案1：UNION ALL 拼接（兼容所有MySQL版本，性能最优） #

待转置的指标数量固定且不多时，直接用UNION ALL拆分每个指标取值是最简单高效的写法，不需要依赖高版本特性：

WITH mydata
AS (SELECT '123' AS id,
1   AS he_has_logo,
0   AS i_have_logo,
1   AS he_has_image,
1   AS i_have_image)
SELECT 'has_logo' AS variable, he_has_logo AS he, i_have_logo AS i FROM mydata
UNION ALL
SELECT 'has_image' AS variable, he_has_image AS he, i_have_image AS i FROM mydata

逻辑说明 #

• 每个SELECT子句单独提取一类指标的结果：手动固定variable列的指标名称，分别读取he_前缀、i_前缀的字段值作为he、i列的取值
• 用UNION ALL而非UNION，避免不必要的去重计算，执行效率更高
• 转置后字段取值完全和源数据对应，仅需调整字段名匹配规则就可以适配同类转置需求

方案2：JSON_TABLE 动态拆解（MySQL 8.0.19及以上版本适用，适合多指标场景） #

如果后续需要新增大量同规则的转置字段（比如新增he_has_avatar/i_have_avatar这类字段），可以用JSON_TABLE把行数据转成JSON后拆解，避免写大量重复的UNION ALL分支：

WITH mydata
AS (SELECT '123' AS id,
1   AS he_has_logo,
0   AS i_have_logo,
1   AS he_has_image,
1   AS i_have_image)
SELECT 
  variable,
  MAX(CASE WHEN prefix = 'he' THEN val END) AS he,
  MAX(CASE WHEN prefix = 'i' THEN val END) AS i
FROM mydata,
JSON_TABLE(
  -- 把行字段转成键值对JSON
  JSON_OBJECT(
    'he_has_logo', he_has_logo,
    'i_have_logo', i_have_logo,
    'he_has_image', he_has_image,
    'i_have_image', i_have_image
  ),
  -- 逐行拆解JSON键值，拆分前缀和指标名
  '$.*' COLUMNS(
    full_key VARCHAR(64) PATH '$',
    val INT PATH '$',
    prefix VARCHAR(16) PATH SUBSTRING_INDEX(full_key, '_', 1),
    variable VARCHAR(64) PATH SUBSTRING(full_key, LOCATE('_', full_key)+1)
  )
) j
GROUP BY variable

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