oracle listagg函数用法

Oracle的LISTAGG函数用于将多行数据合并成单个字符串，并使用指定的分隔符将其连接起来。其基本语法如下：

LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY column_name)

其中：

  • column_name：要合并的列名。

  • delimiter：用于分隔连接的字符串。

  • ORDER BY column_name：可选的子句，用于指定合并结果的排序顺序。

示例

假设我们有一个包含姓名和年龄字段的表employees，我们可以使用LISTAGG函数将所有姓名连接成一个字符串，如下所示：

SELECT LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS all_names FROM employees；

这将返回一个包含所有姓名的字符串，每个姓名之间以逗号加空格分隔，例如："SMITH, JAMES, ADAMS, MARTIN, WARD, MILLER, TURNER, ALLEN, CLARK, BLAKE, JONES, FORD, SCOTT, KING"。

分组使用

LISTAGG函数也可以用于分组数据。例如，如果我们想按部门对员工姓名进行分组并连接，可以这样做：

SELECT deptno, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS all_names FROM employees GROUP BY deptno；

这将返回每个部门的员工姓名，按部门名称排序并以逗号加空格分隔，例如：

DEPTNO ALL_NAMES
10    MILLER, CLARK, KING
20    SMITH, ADAMS, JONES, FORD, SCOTT, JAMES, MARTIN, WARD, TURNER, ALLEN, BLAKE

分析函数用法

LISTAGG函数还可以作为分析函数使用，例如，在窗口函数中：

SELECT deptno, ename, sal, LISTAGG(ename, ', ') WITHIN GROUP (ORDER BY sal) OVER (PARTITION BY deptno) AS names_per_dept
FROM emp；

这将返回每个部门中按薪水排序的员工姓名列表，例如：

DEPTNO  ENAME     SAL NAMES_PER_DEPT
10      MILLER   100 SMITH, CLARK, KING
20      SMITH    200 ADAMS, JONES, FORD, SCOTT, JAMES, MARTIN, WARD, TURNER, ALLEN, BLAKE

注意事项

  • LISTAGG函数在Oracle 11g版本之后可用。如果使用的是较早版本的Oracle，可以考虑使用其他方法来实现类似的功能，如使用SYS_CONNECT_BY_PATH函数或自定义聚合函数等。

  • delimiter参数默认为空(NUL)，如果需要使用其他分隔符，可以显式指定。

Top
×
×