如何在JOIN操作中将多行ID合并为逗号分隔的单行？

核心思路是先对ID表按key分组，把同一key下的多个id合并成逗号分隔的字符串，再将这个聚合后的结果与第二张表做关联，就能避免JOIN后产生重复行。以下是不同SQL方言的具体实现：

MySQL/MariaDB 实现 #

使用GROUP_CONCAT函数完成字符串聚合：

SELECT 
  t.key, 
  t.count, 
  t.location, 
  COALESCE(id_agg.id, '') AS id
FROM second_table t
LEFT JOIN (
  SELECT 
    key, 
    GROUP_CONCAT(id ORDER BY id SEPARATOR ', ') AS id
  FROM id_table
  GROUP BY key
) id_agg ON t.key = id_agg.key;

PostgreSQL 实现 #

使用STRING_AGG函数，注意需将数值类型的id转为文本：

SELECT 
  t.key, 
  t.count, 
  t.location, 
  COALESCE(id_agg.id, '') AS id
FROM second_table t
LEFT JOIN (
  SELECT 
    key, 
    STRING_AGG(id::TEXT, ', ') ORDER BY id AS id
  FROM id_table
  GROUP BY key
) id_agg ON t.key = id_agg.key;

SQL Server 2017+ 实现 #

使用STRING_AGG函数，需转换id的类型：

SELECT 
  t.key, 
  t.count, 
  t.location, 
  COALESCE(id_agg.id, '') AS id
FROM second_table t
LEFT JOIN (
  SELECT 
    key, 
    STRING_AGG(CAST(id AS VARCHAR(MAX)), ', ') WITHIN GROUP (ORDER BY id) AS id
  FROM id_table
  GROUP BY key
) id_agg ON t.key = id_agg.key;

Oracle 实现 #

使用LISTAGG函数：

SELECT 
  t.key, 
  t.count, 
  t.location, 
  COALESCE(id_agg.id, '') AS id
FROM second_table t
LEFT JOIN (
  SELECT 
    key, 
    LISTAGG(id, ', ') WITHIN GROUP (ORDER BY id) AS id
  FROM id_table
  GROUP BY key
) id_agg ON t.key = id_agg.key;

说明 #

• 用LEFT JOIN可保证第二张表的所有记录都被保留，即使某个key在ID表中无对应数据
• COALESCE函数用于处理空值，确保无对应ID的key的id字段显示为空字符串（可按需调整）
• 加入ORDER BY id能让合并后的ID按顺序排列，不需要的话可以去掉

内容的提问来源于stack exchange，提问作者M. Pollino