如何用SELECT语句将MySQL表字段拆分并填充至新列？

方法一：使用窗口函数（推荐，MySQL 8.0+支持） #

利用窗口函数MAX() OVER (PARTITION BY t_id)，在相同t_id的分组内计算对应字段的取值，让每个t_id的所有行都填充上category、subcategory、priority值：

SELECT 
    t_id,
    MAX(CASE WHEN field_value_id = 542 THEN field_value END) OVER (PARTITION BY t_id) AS category,
    MAX(CASE WHEN field_value_id = 11 THEN field_value END) OVER (PARTITION BY t_id) AS subcategory,
    MAX(CASE WHEN field_value_id = 700 THEN field_value END) OVER (PARTITION BY t_id) AS priority,
    field_value,  -- 保留原字段，不需要可删除
    field_value_id  -- 保留原字段，不需要可删除
FROM your_table_name;

执行后，每个t_id的所有行都会显示对应的三类信息，比如t_id=112233的所有行，category列统一为Event，subcategory统一为Information，priority统一为Medium。

方法二：子查询关联（兼容低版本MySQL） #

如果你的MySQL版本不支持窗口函数（如5.7及更早），可以通过子查询分别提取每个t_id对应的三类信息，再与原表关联：

SELECT 
    t1.t_id,
    t2.category,
    t3.subcategory,
    t4.priority,
    t1.field_value,
    t1.field_value_id
FROM your_table_name t1
INNER JOIN (
    SELECT t_id, field_value AS category
    FROM your_table_name
    WHERE field_value_id = 542
    GROUP BY t_id, field_value  -- 去重同t_id下的重复数据
) t2 ON t1.t_id = t2.t_id
INNER JOIN (
    SELECT t_id, field_value AS subcategory
    FROM your_table_name
    WHERE field_value_id = 11
    GROUP BY t_id, field_value
) t3 ON t1.t_id = t3.t_id
INNER JOIN (
    SELECT t_id, field_value AS priority
    FROM your_table_name
    WHERE field_value_id = 700
    GROUP BY t_id, field_value
) t4 ON t1.t_id = t4.t_id;

原问题说明 #

你之前使用的MAX(CASE...)语句未指定分组范围，因此仅在匹配field_value_id的行返回值，其他行为空。通过窗口函数的PARTITION BY t_id或子查询关联的方式，可让相同t_id的所有行都获取到对应类别的值。

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