如何遍历字符串数组执行SQL查询实现A、B表关联匹配返回两表字段

核心实现逻辑分两步：

1. 对表A存储字符串数组的列做**列转行（数组展开）**处理，将单条记录中数组包含的每个字符串拆分为独立行
2. 用拆分出的单个字符串元素和表B的目标匹配字段做关联查询，同时提取两张表需要的返回字段即可

不同数据库的具体实现示例 #

以下示例默认：表A存数组的列名为arr_col，表B用来匹配的字段名为match_val，实际使用时替换为自己的业务字段名即可。

• PostgreSQL
  PG原生支持数组类型，直接用内置的unnest()函数完成数组展开：

  SELECT
    a.*,
    b.*
  FROM A a
  CROSS JOIN unnest(a.arr_col) AS arr_elements(single_str)
  LEFT JOIN B b
    ON b.match_val = arr_elements.single_str;
  

  如果不需要保留数组元素没匹配到B表数据的记录，把LEFT JOIN替换为INNER JOIN即可。
• MySQL 8.0+
  分两种存储场景：
  1. 数组列是JSON类型存储的标准数组：用JSON_TABLE()函数展开

  SELECT
    a.*,
    b.*
  FROM A a
  CROSS JOIN JSON_TABLE(
    a.arr_col,
    '$[*]' COLUMNS (single_str VARCHAR(255) PATH '$')
  ) AS arr_elements
  LEFT JOIN B b
    ON b.match_val = arr_elements.single_str;
  

  2. 数组是逗号分隔的普通字符串（格式如"a,b,c"）：用递归CTE拆分后关联

  WITH RECURSIVE num_seq AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM num_seq WHERE seq < 1000 -- 数值调整为单条记录数组最大元素个数
  ),
  a_expand AS (
    SELECT
      a.*,
      TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(a.arr_col, ',', seq), ',', -1)) AS single_str
    FROM A a
    INNER JOIN num_seq ns
      ON seq <= CHAR_LENGTH(a.arr_col) - CHAR_LENGTH(REPLACE(a.arr_col, ',', '')) + 1
  )
  SELECT
    a_expand.*,
    b.*
  FROM a_expand
  LEFT JOIN B b
    ON b.match_val = a_expand.single_str;
  

• Hive/Spark SQL
  用LATERAL VIEW explode()语法展开数组：

  SELECT
    a.*,
    b.*
  FROM A a
  LATERAL VIEW explode(a.arr_col) t AS single_str
  LEFT JOIN B b
    ON b.match_val = t.single_str;
  

• ClickHouse
  直接用ARRAY JOIN语法完成数组展开：

  SELECT
    a.*,
    b.*
  FROM A a
  ARRAY JOIN a.arr_col AS single_str
  LEFT JOIN B b
    ON b.match_val = single_str;
  

优化提示

• 如果数组元素存在前后空格、大小写不一致等情况，关联前可以用TRIM()、LOWER()等函数做统一清洗，避免匹配漏数
• 数据量较大时，建议给表B的匹配字段加索引，能显著提升关联查询效率
• 低版本MySQL不支持递归CTE的话，可以提前建一张数字辅助表（存储1到足够大的连续整数），替代递归CTE做字符串拆分

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