如何实现数据库中同组多行的字段值逗号分隔拼接合并

核心逻辑是按id_study字段分组，使用数据库内置的字符串聚合函数，分别对同组内的id、writer字段做逗号拼接，以下是主流数据库的具体实现：

注：以下示例默认原表名为study_writer，所有写法都加入了按id排序的逻辑，保证拼接顺序和原数据id升序一致，避免顺序错乱。

• MySQL / MariaDB
  使用GROUP_CONCAT函数实现，writer字段拼接时指定分隔符为逗号+空格，和需求输出格式对齐：

  SELECT
      id_study,
      GROUP_CONCAT(id ORDER BY id) AS merged_id,
      GROUP_CONCAT(writer ORDER BY id SEPARATOR ', ') AS merged_writer
  FROM study_writer
  GROUP BY id_study;
  

  注意：MySQL中GROUP_CONCAT默认最大拼接长度为1024字符，如果同组数据量较大可能出现内容截断，可执行SET SESSION group_concat_max_len = 102400;临时调大当前会话的长度限制。

• PostgreSQL
  使用string_agg函数实现，由于id为数值类型，需要先转为文本类型再做聚合：

  SELECT
      id_study,
      string_agg(id::text, ',' ORDER BY id) AS merged_id,
      string_agg(writer, ', ' ORDER BY id) AS merged_writer
  FROM study_writer
  GROUP BY id_study;
  

• SQL Server 2017及以上版本
  使用STRING_AGG函数实现，排序逻辑写在WITHIN GROUP子句中：

  SELECT
      id_study,
      STRING_AGG(id, ',') WITHIN GROUP (ORDER BY id) AS merged_id,
      STRING_AGG(writer, ', ') WITHIN GROUP (ORDER BY id) AS merged_writer
  FROM study_writer
  GROUP BY id_study;
  

• Oracle 11gR2及以上版本
  使用LISTAGG函数实现：

  SELECT
      id_study,
      LISTAGG(id, ',') WITHIN GROUP (ORDER BY id) AS merged_id,
      LISTAGG(writer, ', ') WITHIN GROUP (ORDER BY id) AS merged_writer
  FROM study_writer
  GROUP BY id_study;
  

以上SQL执行后的输出结果完全匹配需求：

• id_study=1时，merged_id值为1,2，merged_writer值为writer1, writer2
• id_study=2时，merged_id值为3，merged_writer值为writer3

如果需要将拼接后的结果持久化到新表，只需要在对应SELECT语句前加建表语法即可，比如MySQL中可以写为CREATE TABLE study_writer_merged AS [上述SELECT语句]，直接生成合并后的新表。

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