The fuction of listagg.

最新推荐文章于 2025-12-20 13:10:13 发布

最新推荐文章于 2025-12-20 13:10:13 发布 · 81 阅读

文章标签：

本文详细介绍了LISTAGG函数的功能及使用方法。该函数能够将指定组内的订单数据按顺序进行连接，并支持作为单一集合聚合函数操作所有行返回单行结果，作为组集聚合函数为每个分组返回一行结果，或作为分析函数根据查询分区子句中的一个或多个表达式将查询结果集分为若干组。

LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the <measure> column.

As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row.
As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause.
As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause .

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> ed
已写入 file afiedt.buf

1 select deptno, listagg(job,';') within group (order by job) jobs
2 from scott.emp
3 group by deptno
4* order by deptno
SQL> /

DEPTNO
----------
JOBS
--------------------------------------------------------------------------------
10
CLERK;MANAGER;PRESIDENT

20
ANALYST;ANALYST;CLERK;CLERK;MANAGER

30
CLERK;MANAGER;SALESMAN;SALESMAN;SALESMAN;SALESMAN

We can use the distinct in the sub query to remove the duplicated records.
SQL> ed
已写入 file afiedt.buf

1 select deptno, listagg(job,';') within group (order by job) jobs
2 from (select distinct deptno, job from scott.emp)
3 group by deptno
4* order by deptno
SQL> /

DEPTNO
----------
JOBS
--------------------------------------------------------------------------------
10
CLERK;MANAGER;PRESIDENT

20
ANALYST;CLERK;MANAGER

30
CLERK;MANAGER;SALESMAN