如何将表中同列多值拆分生成新行并保留其余列原有取值

需求说明 #

对单行包含多个负责人的记录做拆分处理：为每位负责人单独生成一行记录，拆分时一一对应匹配responsible_id、responsible_name、responsible_weight三个字段的取值，其余列完全保留原对应行的原有数值。
字段分隔规则如下：

• responsible_id、responsible_name 字段的多值以英文逗号+空格 , 作为分隔符
• responsible_weight 字段的多值以英文分号+空格 ; 作为分隔符

原始数据 #

目标输出结果 #

实现方式 #

MySQL 8.0+ 递归CTE实现 #

直接在数据库层处理的话，用递归CTE拆分多值字段即可，执行以下SQL：

WITH RECURSIVE resp_split AS (
    SELECT
        goal_id,
        goal_name,
        owner,
        person_id,
        person_name,
        TRIM(SUBSTRING_INDEX(responsible_id, ',', 1)) AS responsible_id,
        TRIM(SUBSTRING_INDEX(responsible_name, ',', 1)) AS responsible_name,
        TRIM(SUBSTRING_INDEX(responsible_weight, ';', 1)) AS responsible_weight,
        responsible_id AS raw_rid,
        responsible_name AS raw_rname,
        responsible_weight AS raw_rweight,
        1 AS pos
    FROM goal_origin_table
    UNION ALL
    SELECT
        goal_id,
        goal_name,
        owner,
        person_id,
        person_name,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_rid, ',', pos+1), ',', -1)),
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_rname, ',', pos+1), ',', -1)),
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(raw_rweight, ';', pos+1), ';', -1)),
        raw_rid,
        raw_rname,
        raw_rweight,
        pos + 1
    FROM resp_split
    WHERE pos < LENGTH(raw_rid) - LENGTH(REPLACE(raw_rid, ',', '')) + 1
)
SELECT goal_id, goal_name, owner, person_id, person_name, responsible_id, responsible_name, responsible_weight
FROM resp_split
ORDER BY goal_id, pos;

把代码里的goal_origin_table替换成你实际的原表名即可。

Python pandas实现 #

如果是离线处理表格数据，用pandas的拆分+展开功能实现更简单，代码如下：

import pandas as pd

# 读取原数据，替换成你实际的文件读取逻辑
df = pd.read_excel("你的原表文件路径.xlsx")

# 按对应分隔符拆分三个负责人相关字段为列表
df["responsible_id"] = df["responsible_id"].str.split(", ")
df["responsible_name"] = df["responsible_name"].str.split(", ")
df["responsible_weight"] = df["responsible_weight"].str.split("; ")

# 按拆分后的列表展开行，其余列自动保留原值
df_result = df.explode(
    ["responsible_id", "responsible_name", "responsible_weight"],
    ignore_index=True
)

# 导出结果
df_result.to_excel("拆分后结果.xlsx", index=False)

重要提示：拆分前务必校验三个负责人字段的多值数量完全一致，否则会出现值匹配错位的问题，影响数据准确性。

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