首先选择所需的DISTINCT值，然后将LISTAGG应用于它们。这是一个基于Scott模式的示例。
SQL> -- Duplicate jobs within the departmentSQL> select deptno, listagg(job, ', ') within group (order by job) jobs 2 from emp 3 group by deptno; DEPTNO JOBS---------- ------------------------------------------------------------ 10 CLERK, MANAGER, PRESIDENT 20 ANALYST, ANALYST, CLERK, CLERK, MANAGER 30 CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMANSQL>SQL> -- This won't work - DISTINCT can't be used in LISTAGGSQL> select deptno, listagg(distinct job, ', ') within group (order by job) jobs 2 from emp 3 group by deptno;select deptno, listagg(distinct job, ', ') within group (order by job) jobs *ERROR at line 1:ORA-30482: DISTINCT option not allowed for this functionSQL>SQL> -- So - select distinct jobs first, then apply LISTAGG to itSQL> select x.deptno, listagg(x.job, ', ') within group (order by x.job) jobs 2 from (select distinct deptno, job 3 from emp) x 4 group by x.deptno; DEPTNO JOBS---------- ------------------------------------------------------------ 10 CLERK, MANAGER, PRESIDENT 20 ANALYST, CLERK, MANAGER 30 CLERK, MANAGER, SALESMANSQL>
