一、string_agg在 PostgreSQL 中把多行数据中的某个字段合并成一个字符串字段展示最常用且最标准的函数是string_agg。核心函数string_agg这个函数可以将选定的列的值连接起来并用你指定的分隔符隔开。1. 基本语法string_agg(要合并的字段, 分隔符)注意第一个参数必须是字符类型text, varchar。如果是数字或日期需要转换类型例如id::text。使用场景与示例假设你有一个学生表students数据如下class_name (班级)student_name (学生)一班张三一班李四二班王五场景 A简单的合并 (所有数据合为一行)如果你想把所有学生名字合并成一串SELECT string_agg(student_name, , ) AS all_studentsFROM students;结果张三, 李四, 王五场景 B分组合并 (配合 GROUP BY)这是最常用的场景。比如你想看每个班级都有哪些学生SELECTclass_name,string_agg(student_name, | ) AS students_listFROM studentsGROUP BY class_name;结果class_namestudents_list一班张三二班王五进阶技巧1. 指定排序 (WITHIN GROUP)默认情况下合并后的顺序是不确定的。如果你希望名字按拼音或 ID 排序需要使用WITHIN GROUPSELECTclass_name,string_agg(student_name, , ) WITHIN GROUP (ORDER BY student_name)FROM studentsGROUP BY class_name;2. 去重 (DISTINCT)如果有重复数据你只想保留唯一的SELECT string_agg(DISTINCT student_name, , ) FROM students;3. 处理非文本类型如果字段是数字比如合并 ID必须强转为文本-- 错误写法string_agg(user_id, ,)-- 正确写法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_aggOracle 在这方面的实现经历了几次演变虽然现在的版本已经非常强大但语法的确比 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-014894000字节限制2. 语法差异详解PostgreSQL 写法PG 的写法非常直观排序和去重都很灵活。SELECTdept_id,string_agg(DISTINCT user_name, , ORDER BY user_name)FROM usersGROUP BY dept_id;Oracle 写法Oracle 使用listagg。最麻烦的一点是排序子句WITHIN GROUP是强制性的即使你不在乎顺序也要写。SELECTdept_id,listagg(user_name, ,) WITHIN GROUP (ORDER BY user_name) AS usersFROM usersGROUP BY dept_id;3. Oracle 为什么被认为“麻烦”Oracle 开发者通常会遇到以下三个痛点① 不支持 DISTINCT (去重麻烦)如果在 Oracle 的listagg里直接写DISTINCT会报错。你必须先在内层查询去重再在外层合并-- Oracle 必须这样写SELECT dept_id, listagg(user_name, ,) WITHIN GROUP (ORDER BY user_name)FROM (SELECT DISTINCT dept_id, user_name FROM users) -- 先去重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语法较严谨强制排序但在处理大数据量和去重时显得笨重。
PostgreSQL：字符串拼接-string_agg
一、string_agg在 PostgreSQL 中把多行数据中的某个字段合并成一个字符串字段展示最常用且最标准的函数是string_agg。核心函数string_agg这个函数可以将选定的列的值连接起来并用你指定的分隔符隔开。1. 基本语法string_agg(要合并的字段, 分隔符)注意第一个参数必须是字符类型text, varchar。如果是数字或日期需要转换类型例如id::text。使用场景与示例假设你有一个学生表students数据如下class_name (班级)student_name (学生)一班张三一班李四二班王五场景 A简单的合并 (所有数据合为一行)如果你想把所有学生名字合并成一串SELECT string_agg(student_name, , ) AS all_studentsFROM students;结果张三, 李四, 王五场景 B分组合并 (配合 GROUP BY)这是最常用的场景。比如你想看每个班级都有哪些学生SELECTclass_name,string_agg(student_name, | ) AS students_listFROM studentsGROUP BY class_name;结果class_namestudents_list一班张三二班王五进阶技巧1. 指定排序 (WITHIN GROUP)默认情况下合并后的顺序是不确定的。如果你希望名字按拼音或 ID 排序需要使用WITHIN GROUPSELECTclass_name,string_agg(student_name, , ) WITHIN GROUP (ORDER BY student_name)FROM studentsGROUP BY class_name;2. 去重 (DISTINCT)如果有重复数据你只想保留唯一的SELECT string_agg(DISTINCT student_name, , ) FROM students;3. 处理非文本类型如果字段是数字比如合并 ID必须强转为文本-- 错误写法string_agg(user_id, ,)-- 正确写法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_aggOracle 在这方面的实现经历了几次演变虽然现在的版本已经非常强大但语法的确比 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-014894000字节限制2. 语法差异详解PostgreSQL 写法PG 的写法非常直观排序和去重都很灵活。SELECTdept_id,string_agg(DISTINCT user_name, , ORDER BY user_name)FROM usersGROUP BY dept_id;Oracle 写法Oracle 使用listagg。最麻烦的一点是排序子句WITHIN GROUP是强制性的即使你不在乎顺序也要写。SELECTdept_id,listagg(user_name, ,) WITHIN GROUP (ORDER BY user_name) AS usersFROM usersGROUP BY dept_id;3. Oracle 为什么被认为“麻烦”Oracle 开发者通常会遇到以下三个痛点① 不支持 DISTINCT (去重麻烦)如果在 Oracle 的listagg里直接写DISTINCT会报错。你必须先在内层查询去重再在外层合并-- Oracle 必须这样写SELECT dept_id, listagg(user_name, ,) WITHIN GROUP (ORDER BY user_name)FROM (SELECT DISTINCT dept_id, user_name FROM users) -- 先去重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语法较严谨强制排序但在处理大数据量和去重时显得笨重。
