PostgreSQL：字符串拼接-string_agg

一、string_agg

在 PostgreSQL 中，把多行数据中的某个字段合并成一个字符串（字段）展示，最常用且最标准的函数是 string_agg。
核心函数：string_agg

这个函数可以将选定的列的值连接起来，并用你指定的分隔符隔开。
1. 基本语法

1. string_agg(要合并的字段, '分隔符')

复制代码

• 注意：第一个参数必须是字符类型（text, varchar）。如果是数字或日期，需要转换类型（例如 id::text）。
  

使用场景与示例

假设你有一个学生表 students，数据如下：
class_name (班级)student_name (学生)一班张三一班李四二班王五场景 A：简单的合并 (所有数据合为一行)

如果你想把所有学生名字合并成一串：

1. SELECT string_agg(student_name, ', ') AS all_students
2. FROM students;

复制代码

结果：
张三, 李四, 王五
场景 B：分组合并 (配合 GROUP BY)

这是最常用的场景。比如，你想看每个班级都有哪些学生：

1. SELECT
2.     class_name,
3.     string_agg(student_name, ' | ') AS students_list
4. FROM students
5. GROUP BY class_name;

复制代码

结果：
class_namestudents_list一班张三二班王五进阶技巧

1. 指定排序 (WITHIN GROUP)

默认情况下，合并后的顺序是不确定的。如果你希望名字按拼音或 ID 排序，需要使用 WITHIN GROUP：

1. SELECT
2.     class_name,
3.     string_agg(student_name, ', ') WITHIN GROUP (ORDER BY student_name)
4. FROM students
5. GROUP BY class_name;

复制代码

2. 去重 (DISTINCT)

如果有重复数据，你只想保留唯一的：

1. SELECT string_agg(DISTINCT student_name, ', ') FROM students;

复制代码

3. 处理非文本类型

如果字段是数字（比如合并 ID），必须强转为文本：

1. -- 错误写法：string_agg(user_id, ',')
2. -- 正确写法：
3. SELECT string_agg(user_id::text, ',') FROM users;

复制代码

其他类似函数

虽然 string_agg 是首选，但根据需求你可能还会用到：

• array_agg(): 如果你不想得到由逗号分隔的字符串，而是想要一个数组格式（例如方便程序后端处理），可以使用这个。
  
• 输出示例: {张三,李四,王五} (PostgreSQL 数组格式)
  
• array_to_string(array_agg(字段), ','): 这是旧版本 PG (9.0之前) 的写法，现在基本都被 string_agg 取代了。
  

二、与oracle的对比

确实，相比 PostgreSQL 的 string_agg，Oracle 在这方面的实现经历了几次演变，虽然现在的版本已经非常强大，但语法的确比 PG 稍微复杂一点。
以下是 PostgreSQL 和 Oracle 在“行转列（字符合并）”上的对比：
1. 函数对比表

特性PostgreSQLOracle (11g R2 及以上)主要函数string_agg(column, separator)listagg(column, separator)排序方式WITHIN GROUP (ORDER BY ...)WITHIN GROUP (ORDER BY ...)类型限制必须是 text 类型（否则需显式转换）支持数值、字符等，自动转换去重 (DISTINCT)原生支持 string_agg(DISTINCT ...)不支持（需先写子查询去重）超长处理自动处理（受限于 text 最大长度）容易触发 ORA-01489（4000字节限制）2. 语法差异详解

PostgreSQL 写法

PG 的写法非常直观，排序和去重都很灵活。

1. SELECT
2.     dept_id,
3.     string_agg(DISTINCT user_name, ',' ORDER BY user_name)
4. FROM users
5. GROUP BY dept_id;

复制代码

Oracle 写法

Oracle 使用 listagg。最麻烦的一点是排序子句 WITHIN GROUP 是强制性的（即使你不在乎顺序也要写）。

1. SELECT
2.     dept_id,
3.     listagg(user_name, ',') WITHIN GROUP (ORDER BY user_name) AS users
4. FROM users
5. GROUP BY dept_id;

复制代码

3. Oracle 为什么被认为“麻烦”？

Oracle 开发者通常会遇到以下三个痛点：
① 不支持 DISTINCT (去重麻烦)

如果在 Oracle 的 listagg 里直接写 DISTINCT 会报错。你必须先在内层查询去重，再在外层合并：

1. -- Oracle 必须这样写：
2. SELECT dept_id, listagg(user_name, ',') WITHIN GROUP (ORDER BY user_name)
3. FROM (SELECT DISTINCT dept_id, user_name FROM users) -- 先去重
4. GROUP BY dept_id;

复制代码

② 4000 字节长度限制 (报错麻烦)

Oracle 的 listagg 默认返回 VARCHAR2，最大长度通常是 4000 字节。如果合并的数据太多，会直接报错 ORA-01489: result of string concatenation is too long。

• 12c 之后的解决办法：增加了 ON OVERFLOW TRUNCATE 关键字，防止报错（但会截断数据）。
  
• 终极解决办法：使用复杂的 XMLAGG 函数转成 CLOB 类型，那段代码写起来非常痛苦。
  

③ 旧版本的历史债

如果是在非常老的 Oracle 版本（11g 之前），甚至没有 listagg，那时候需要用 WM_CONCAT（未公开且不推荐）或者更恐怖的 SYS_CONNECT_BY_PATH（利用递归树查询来模拟）。
总结

• PostgreSQL：追求简洁、功能全面（原生支持去重）。
  
• Oracle：语法较严谨（强制排序），但在处理大数据量和去重时显得笨重。
  

您现在的业务场景中，合并后的数据量大吗？ 如果单组数据合并后可能超过 4000 个字符，Oracle 端需要特别注意处理。

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！