Oracle 19c LISTAGG 函数中distinct

最新推荐文章于 2026-05-10 21:19:44 发布

原创最新推荐文章于 2026-05-10 21:19:44 发布 · 727 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql #数据库 #oracle

DBA 专栏收录该内容

183 篇文章

订阅专栏

本文介绍Oracle数据库中LISTAGG函数如何通过不同方法去除聚合结果中的重复项，包括使用子查询获取唯一值及直接利用DISTINCT关键字简化操作。

Descriptions:

The LISTAGG aggregate function now supports duplicate elimination by using the new DISTINCT keyword.

Step1 :Notice the repeated results as below:

[oracle@oracle-db-19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 19:47:44 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> set pagesize 200
SQL> set linesize 200
SQL> alter session set container=PDB1;

Session altered.

SQL> conn scott/tiger@pdb1;
Connected.
SQL> 
SQL> column dname for a30
SQL> column jobs for a65
SQL> select d.dname,   
  2         listagg (e.job,', ' on overflow truncate with count)     
  3                  within group (order by e.job) jobs  
  4    from scott.dept d, scott.emp e     
  5   where d.deptno = e.deptno     
  6   group by d.dname;

DNAME                          JOBS
------------------------------ -----------------------------------------------------------------
ACCOUNTING                     CLERK, MANAGER, PRESIDENT
RESEARCH                       ANALYST, ANALYST, CLERK, CLERK, MANAGER
SALES                          CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN

SQL>

Step2 : To remove the duplicates, we can use a nested select to get just the unique jobs for the LISTAGG function.

SQL> 
SQL> SELECT d.dname,   
  2         (select LISTAGG(job,', ' ON OVERFLOW TRUNCATE WITH COUNT)    
  3                 WITHIN GROUP (ORDER BY job)   
  4            from (select unique job job   
  5                    from scott.emp e  
  6                   where d.deptno = e.deptno)) jobs  
  7    FROM scott.dept d;

DNAME                          JOBS
------------------------------ -----------------------------------------------------------------
ACCOUNTING                     CLERK, MANAGER, PRESIDENT
RESEARCH                       ANALYST, CLERK, MANAGER
SALES                          CLERK, MANAGER, SALESMAN
OPERATIONS

SQL>

Step3 we can now just use DISTINCT within our LISTAGG to remove any repeated values.

SQL> select d.dname,   
  2         listagg (DISTINCT e.job,', ' on overflow truncate with count)     
  3                  within group (order by e.job) jobs  
  4    from scott.dept d, scott.emp e     
  5   where d.deptno = e.deptno     
  6   group by d.dname;

DNAME                          JOBS
------------------------------ -----------------------------------------------------------------
ACCOUNTING                     CLERK, MANAGER, PRESIDENT
RESEARCH                       ANALYST, CLERK, MANAGER
SALES                          CLERK, MANAGER, SALESMAN

SQL>