如何用纯SQL将相同ID的所有结果高效存入数组，避免循环查询？

核心思路是使用SQL原生的分组聚合函数一次性完成数据聚合与插入，仅需扫描原表一次，无多余子查询开销，性能远高于循环逐次查询的方案。

不同数据库实现代码 #

MySQL #

INSERT INTO new_table(ad_id, _duplicate_id)
SELECT 
  ad_id,
  CONCAT('{', GROUP_CONCAT(duplicate_id SEPARATOR ', '), '}') AS _duplicate_id
FROM 你的原表名
GROUP BY ad_id;

如果需要存储为JSON数组格式，可将GROUP_CONCAT部分替换为JSON_ARRAYAGG(duplicate_id)

PostgreSQL #

INSERT INTO new_table(ad_id, _duplicate_id)
SELECT 
  ad_id,
  '{' || STRING_AGG(duplicate_id::TEXT, ', ') || '}' AS _duplicate_id
FROM 你的原表名
GROUP BY ad_id;

如果需要存储为数组类型，可将拼接部分替换为ARRAY_AGG(duplicate_id)

Oracle #

INSERT INTO new_table(ad_id, _duplicate_id)
SELECT 
  ad_id,
  '{' || LISTAGG(duplicate_id, ', ') WITHIN GROUP (ORDER BY duplicate_id) || '}' AS _duplicate_id
FROM 你的原表名
GROUP BY ad_id;

注意事项 #

• 替换代码中的你的原表名为实际业务表名
• 若duplicate_id为数值类型，部分数据库需要显式转为字符串后再进行拼接操作
• 数据量越大，该方案对比循环方案的性能优势越明显

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