SQL如何拆分逗号分隔值并关联表查询获取指定列的聚合结果？

你需要先拆分T1中逗号分隔的product_id字段为单个id的单行数据，再和T2关联过滤，最后分组取val最小值即可。

具体实现步骤 #

• 第一步：拆分T1的product_id字段，将逗号分隔的多值拆分为一行一个独立id
• 第二步：将拆分后的结果和T2关联，过滤出仅在T2中存在的t_product_id对应的数据
• 第三步：按product_id分组，取val的最小值

示例SQL（以MySQL 8.0+为例，支持递归CTE拆分） #

WITH RECURSIVE split_ids AS (
    -- 基础查询：定位每个product_id第一个逗号的位置
    SELECT 
        product_id AS original_id,
        val,
        1 AS start_pos,
        IF(LOCATE(',', product_id) > 0, LOCATE(',', product_id), LENGTH(product_id) + 1) AS end_pos
    FROM T1
    UNION ALL
    -- 递归拆分：循环处理剩余的逗号分隔值
    SELECT 
        original_id,
        val,
        end_pos + 1 AS start_pos,
        IF(LOCATE(',', original_id, end_pos + 1) > 0, LOCATE(',', original_id, end_pos + 1), LENGTH(original_id) + 1) AS end_pos
    FROM split_ids
    WHERE end_pos <= LENGTH(original_id)
),
t1_single_id AS (
    -- 提取拆分后的单个id，去除首尾空格避免关联出错
    SELECT 
        TRIM(SUBSTRING(original_id, start_pos, end_pos - start_pos)) AS product_id,
        val
    FROM split_ids
)
-- 关联T2后分组取最小值
SELECT 
    t.product_id,
    MIN(t.val) AS value
FROM t1_single_id t
INNER JOIN T2 t2 ON t.product_id = t2.t_product_id
GROUP BY t.product_id;

其他数据库简化写法参考 #

• PostgreSQL 可以用STRING_TO_TABLE函数直接拆分：

  SELECT 
      s.product_id,
      MIN(t1.val) AS value
  FROM T1 t1,
       UNNEST(STRING_TO_ARRAY(t1.product_id, ',')) s(product_id)
  INNER JOIN T2 t2 ON s.product_id = t2.t_product_id
  GROUP BY s.product_id;
  

• Hive/Spark SQL 可以用EXPLODE+SPLIT函数拆分：

  SELECT 
      s.product_id,
      MIN(t1.val) AS value
  FROM T1 t1
  LATERAL VIEW EXPLODE(SPLIT(t1.product_id, ',')) s AS product_id
  INNER JOIN T2 t2 ON s.product_id = t2.t_product_id
  GROUP BY s.product_id;
  

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