【ORACLE】listagg() 函数

Oracle 数据库中的 LISTAGG 函数是一个聚合函数，它用于将多个行的字符串值合并成一个单一的字符串。这对于生成报告或创建列表非常有用，例如，将同一类别的所有项合并成一个逗号分隔的字符串。

语法

LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY column)

• expression：要聚合的字符串表达式。
• delimiter：用于分隔合并后的字符串值的分隔符。
• column：用于排序聚合字符串的列。

选项

• WITHIN GROUP：这是必需的，用于指定聚合操作的分组方式。
• ORDER BY：这是可选的，用于在聚合之前对行进行排序。

溢出处理

• ON OVERFLOW：这是可选的，用于指定当结果超过最大长度限制时如何处理溢出。常见的选项有：
  • TRUNCATE：截断结果，不包括最后一个分隔符。
  • ERROR：如果结果超过最大长度，抛出错误。
  • NULL：如果结果超过最大长度，返回 NULL。

示例

假设有一个名为 employees 的表，其中包含员工的名字（name）和部门（department）。

1. 基本聚合：

   SELECT department, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS department_names
   FROM employees
   GROUP BY department;
   

   这个查询将返回每个部门的员工名字列表，名字按字母顺序排列，用逗号和空格分隔。

2. 处理溢出：

   SELECT department, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) 
   ON OVERFLOW TRUNCATE AS department_names
   FROM employees
   GROUP BY department;
   

   如果聚合后的字符串超过了 Oracle 的最大长度限制（默认为 4000 字符），则结果将被截断。

3. 不排序：

   SELECT department, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS department_names
   FROM employees
   GROUP BY department;
   

   如果不使用 ORDER BY 子句，聚合的顺序将不确定。

4. 包含 NULL 值：

   LISTAGG 默认会忽略 NULL 值。如果你想在结果中包含 NULL 值，可以使用 NULLIF 函数来转换 NULL 值为一个特定的字符串。

   SELECT department, LISTAGG(NULLIF(name, 'NULL'), ', ') WITHIN GROUP (ORDER BY name) AS department_names
   FROM employees
   GROUP BY department;
   

   在这个例子中，如果 name 是 NULL，它将被替换为字符串 ‘NULL’。

LISTAGG 函数是 Oracle 中处理字符串聚合的强大工具，可以帮助你创建复杂的报告和列表。