Oracle LISTAGG函数详解

LISTAGG 是 Oracle 数据库（从 11g R2 版本开始引入）中一个非常强大且常用的聚合函数。它的核心功能是：将多行数据合并（连接）成单个字符串。

简单来说，它实现了“行转列”或“多行合并为一行”的效果。

---

1. 核心语法

LISTAGG(column_name, [delimiter]) WITHIN GROUP (ORDER BY sort_expression)

• column_name：你想要合并的列名。
• delimiter：可选参数。用于分隔每个值的分隔符，比如逗号 ,、空格 ' '、连字符 '-' 等。如果省略，则所有值会直接连接在一起，没有分隔。
• WITHIN GROUP (ORDER BY ...)：必需。指定合并这些值时，按照哪个（或哪些）列进行排序。

---

2. 基本示例

假设我们有一个 employees 表，数据如下：

EMPLOYEE_ID	NAME	DEPARTMENT_ID
101	Alice	10
102	Bob	10
103	Charlie	20
104	David	10
105	Eve	20

需求： 我想知道每个部门都有哪些员工，将员工姓名用逗号连接起来。

SELECT 
    DEPARTMENT_ID,
    LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY NAME) AS Employees
FROM 
    employees
GROUP BY 
    DEPARTMENT_ID;

查询结果：

DEPARTMENT_ID	Employees
10	Alice, Bob, David
20	Charlie, Eve

解释：

• GROUP BY DEPARTMENT_ID 确保了是按部门进行分组聚合。
• LISTAGG(NAME, ', ') 表示将 NAME 列的值用 逗号+空格 连接起来。
• WITHIN GROUP (ORDER BY NAME) 表示在连接之前，先将每个部门内的员工姓名按字母顺序排序。所以部门10的结果是 Alice, Bob, David 而不是 Alice, David, Bob。

---

3. 处理重复值与排序

LISTAGG 会忠实地合并所有行，包括重复值。如果你想去重，需要在内层查询中使用 DISTINCT。

示例： 假设我们有一个包含重复技能的表，我们想列出每个员工掌握的所有不重复的技能，按字母顺序排列。

-- 假设表结构：employee_skills(employee_name, skill)
SELECT 
    employee_name,
    LISTAGG(skill, ', ') WITHIN GROUP (ORDER BY skill) AS skills -- 没有去重
FROM 
    employee_skills
GROUP BY 
    employee_name;

-- 使用 DISTINCT 去重
SELECT 
    employee_name,
    LISTAGG(DISTINCT skill, ', ') WITHIN GROUP (ORDER BY skill) AS unique_skills
FROM 
    employee_skills
GROUP BY 
    employee_name;

注意：在 Oracle 19c 及更高版本中，LISTAGG 直接支持 DISTINCT 关键字。在更早的版本中，你可能需要借助子查询先进行去重。

---

4. 处理超长字符串错误（ORA-01489）

这是使用 LISTAGG 时最常见的一个问题。由于 LISTAGG 返回的是 VARCHAR2 类型，而 VARCHAR2 在 SQL 中有最大长度限制（通常是 4000 字节）。当合并后的字符串超过这个限制时，就会报错：

ORA-01489: result of string concatenation is too long

解决方案：

a) 使用 ON OVERFLOW 子句（Oracle 12c R2 及以上）
这是最现代和推荐的解决方案。你可以指定当字符串过长时的处理方式。

SELECT 
    DEPARTMENT_ID,
    LISTAGG(NAME, ', ' ON OVERFLOW TRUNCATE '...') -- 超出部分截断，并用‘...’表示
           WITHIN GROUP (ORDER BY NAME) AS Employees
FROM 
    employees
GROUP BY 
    DEPARTMENT_ID;

ON OVERFLOW 的选项：

• TRUNCATE '...'：截断超出的部分，并用指定的字符串（如 ...）表示被截断了。
• ERROR（默认）：抛出 ORA-01489 错误。

b) 在应用层处理
如果数据库版本较低，可以考虑不直接在 SQL 中完全合并，而是获取部分数据后在应用程序（如 Java, Python）中进行拼接和处理。

---

5. 与其他函数的结合

LISTAGG 经常和 OVER (PARTITION BY ...) 分析函数子句一起使用，来实现更复杂的分组。

示例： 我们不仅想知道每个部门的员工列表，还想在每一行员工数据后面都显示他所在部门的完整员工列表。

SELECT 
    EMPLOYEE_ID,
    NAME,
    DEPARTMENT_ID,
    LISTAGG(NAME, ', ') WITHIN GROUP (ORDER BY NAME) 
        OVER (PARTITION BY DEPARTMENT_ID) AS Dept_Employees
FROM 
    employees
ORDER BY 
    DEPARTMENT_ID, EMPLOYEE_ID;

查询结果：

EMPLOYEE_ID	NAME	DEPARTMENT_ID	Dept_Employees
101	Alice	10	Alice, Bob, David
102	Bob	10	Alice, Bob, David
104	David	10	Alice, Bob, David
103	Charlie	20	Charlie, Eve
105	Eve	20	Charlie, Eve

可以看到，Dept_Employees 列在每个部门内是重复的，它展示了该部门的所有员工。

---

总结

特性	描述
功能	将多行数据合并成一个字符串。
类型	聚合函数，也可作为分析函数使用。
核心语法	LISTAGG(列, [分隔符]) WITHIN GROUP (ORDER BY 排序列)
主要用途	制作逗号分隔列表、生成报表、数据拼接。
常见问题	结果字符串超长（ORA-01489）。
解决方案	使用 ON OVERFLOW TRUNCATE（12c R2+）或在应用层处理。
去重	在 Oracle 19c+ 中可直接使用 LISTAGG(DISTINCT ...)。

LISTAGG 是一个非常实用的函数，特别适合在制作报表、数据导出或需要将关系型数据扁平化为字符串的场景中使用。希望这个讲解对你有帮助！