The fuction of listagg.

来源：互联网发布：淘宝装修市场编辑：程序博客网时间：2024/05/22 11:02

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