• Oracle 19c LISTAGG 函数中distinct

    Descriptions:

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

    Step1 :Notice the repeated results as below:

    1. [oracle@oracle-db-19c ~]$ sqlplus / as sysdba
    2.  
    3. SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 19:47:44 2022
    4. Version 19.3.0.0.0
    5.  
    6. Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    7.  
    8.  
    9. Connected to:
    10. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    11. Version 19.3.0.0.0
    12.  
    13. SQL> set pagesize 200
    14. SQL> set linesize 200
    15. SQL> alter session set container=PDB1;
    16.  
    17. Session altered.
    18.  
    19. SQL> conn scott/tiger@pdb1;
    20. Connected.
    21. SQL> 
    22. SQL> column dname for a30
    23. SQL> column jobs for a65
    24. SQL> select d.dname,   
    25.   2         listagg (e.job,', ' on overflow truncate with count)     
    26.   3                  within group (order by e.job) jobs  
    27.   4    from scott.dept d, scott.emp e     
    28.   5   where d.deptno = e.deptno     
    29.   6   group by d.dname;
    30.  
    31. DNAME                          JOBS
    32. ------------------------------ -----------------------------------------------------------------
    33. ACCOUNTING                     CLERK, MANAGER, PRESIDENT
    34. RESEARCH                       ANALYST, ANALYST, CLERK, CLERK, MANAGER
    35. SALES                          CLERK, MANAGER, SALESMAN, SALESMAN, SALESMAN, SALESMAN
    36.  
    37. SQL>

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

    1. SQL> 
    2. SQL> SELECT d.dname,   
    3.   2         (select LISTAGG(job,', ' ON OVERFLOW TRUNCATE WITH COUNT)    
    4.   3                 WITHIN GROUP (ORDER BY job)   
    5.   4            from (select unique job job   
    6.   5                    from scott.emp e  
    7.   6                   where d.deptno = e.deptno)) jobs  
    8.   7    FROM scott.dept d;
    9.  
    10. DNAME                          JOBS
    11. ------------------------------ -----------------------------------------------------------------
    12. ACCOUNTING                     CLERK, MANAGER, PRESIDENT
    13. RESEARCH                       ANALYST, CLERK, MANAGER
    14. SALES                          CLERK, MANAGER, SALESMAN
    15. OPERATIONS
    16.  
    17. SQL>

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

    1. SQL> select d.dname,   
    2.   2         listagg (DISTINCT e.job,', ' on overflow truncate with count)     
    3.   3                  within group (order by e.job) jobs  
    4.   4    from scott.dept d, scott.emp e     
    5.   5   where d.deptno = e.deptno     
    6.   6   group by d.dname;
    7.  
    8. DNAME                          JOBS
    9. ------------------------------ -----------------------------------------------------------------
    10. ACCOUNTING                     CLERK, MANAGER, PRESIDENT
    11. RESEARCH                       ANALYST, CLERK, MANAGER
    12. SALES                          CLERK, MANAGER, SALESMAN
    13.  
    14. SQL>
  • 相关阅读:
    企业怎么优化固定资产管理
    JDK17新特性
    Mysql 和 Redis 数据如何保持一致
    【秋招必备】JVM性能调优面试题（2022最新版）
    传输层协议：TCP/IP协议，UDP的协议
    元宇宙iwemeta：风口上的脑机接口，偷偷的解密大脑
    VsCode与Sublime编辑器优缺点对比
    1040 Longest Symmetric String（动态规划）
    中级xss绕过【xss Game】
    致力于成为某个细分行业最牛逼的程序员，您该如何实现？
  • 原文地址：https://blog.csdn.net/u011868279/article/details/127928556