多分隔值列跨表映射：基于US_english匹配Table1列值的最优方案咨询

核心思路：先把Table1中逗号分隔的单词拆成单独行，和Table2的US_english列做匹配，再将匹配结果聚合回原表的matched列。以下是两种常用场景的具体实现：

场景1：SQL实现（以MySQL为例） #

适合数据存储在数据库中的情况，无需导出数据，效率更高。

• 第一步：拆分逗号分隔的单词为多行
  用递归CTE拆分每个行的words字段，生成单独的单词行：

  WITH split_words AS (
    SELECT 
      id, -- 假设Table1有主键id用于关联
      TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(t1.words, ',', n.n), ',', -1)) AS word
    FROM Table1 t1
    CROSS JOIN (
      SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 -- 根据你的数据最大单词数调整数量
    ) n
    WHERE n.n <= LENGTH(t1.words) - LENGTH(REPLACE(t1.words, ',', '')) + 1
  )
  

• 第二步：匹配并聚合结果
  关联Table2的US_english列，将匹配到的单词重新聚合成逗号分隔的字符串：

  SELECT 
    t1.id,
    t1.words,
    GROUP_CONCAT(DISTINCT sw.word SEPARATOR ',') AS matched
  FROM Table1 t1
  LEFT JOIN split_words sw ON t1.id = sw.id
  LEFT JOIN Table2 t2 ON sw.word = t2.US_english
  GROUP BY t1.id, t1.words;
  

  注：用LEFT JOIN会保留Table1所有行，没有匹配到的matched字段会是NULL；如果只需要保留有匹配结果的行，换成INNER JOIN即可。

场景2：Python Pandas实现 #

适合本地小数据集处理，代码灵活易修改。

• 第一步：拆分逗号分隔字段为多行

  import pandas as pd
  
  # 读取两张表（根据实际文件格式调整读取方式）
  df1 = pd.read_csv('table1.csv')
  df2 = pd.read_csv('table2.csv')
  
  # 拆分words列，保留原表索引以便后续合并
  df_split = df1['words'].str.split(',', expand=True).stack().reset_index(level=1, drop=True).rename('word')
  df_split = df_split.str.strip()  # 去除单词前后的空格
  df_split = df_split.reset_index()
  

• 第二步：匹配并聚合回原表

  # 筛选出在Table2的US_english列中存在的单词
  matched_words = df_split[df_split['word'].isin(df2['US_english'])]
  # 按原表索引聚合，将匹配到的单词合并成逗号分隔的字符串
  df_matched = matched_words.groupby('index')['word'].agg(','.join).rename('matched')
  # 合并到原表，生成最终的matched列
  df1 = df1.join(df_matched, how='left')
  

  注：how='left'保留原表所有行，无匹配的matched列会显示NaN；若只需保留有匹配的行，改为how='inner'。

方案选择建议 #

• 大数据量或数据在数据库中：优先用SQL方案，数据库原生处理效率远高于本地导出处理。
• 小数据集或需要快速验证调整：用Pandas方案，代码修改和调试更便捷。

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