函数

刘欢2025/11/7原创小于 1 分钟约 188 字

1. LISTAGG 多行合并单行

LISTAGG函数可以将多行数据合并成一个由分隔符分隔的字符串。例如，假设你有一个员工表 employees，包含 department_id 和 employee_name 两个字段，你想根据 department_id 将所有员工的名字合并成一行

SELECT department_id,
       LISTAGG(employee_name, ', ') WITHIN GROUP (ORDER BY employee_name) AS employee_names
FROM employees
GROUP BY department_id;

2. SYS_CONNECT_BY_PATH 树形结构

如果你需要处理的是树形结构的数据（例如部门和子部门），可以使用 SYS_CONNECT_BY_PATH 函数

SELECT department_id,
       SYS_CONNECT_BY_PATH(employee_name, ', ') AS employee_names
FROM employees
START WITH parent_department_id IS NULL
CONNECT BY PRIOR department_id = parent_department_id;