我如何从listagg删除重复项 [英] How I remove duplicates from listagg

问题描述

这是我的代码:

SELECT rule,
       DATASET,
       type,
       LISTAGG(source,';') within GROUP (ORDER BY source) AS source,
       TARGET, 
       LISTAGG(filter,';') within GROUP (ORDER BY filter)  AS filter
FROM demo_table;

我的问题是使用的两个listagg().如果有两个源和一个过滤器，则给出重复的过滤器，反之亦然.我可以添加什么以仅具有确切数目的源和过滤器?

My problem is with the two listagg() that I have used. In case of two sources and one filter it is giving duplicate filter and vice versa. What can I add to have only exact number of source and filter?

推荐答案

首先选择所需的DISTINCT值，然后将LISTAGG应用于它们.这是一个基于Scott模式的示例.

First select DISTINCT values you need, then apply LISTAGG to them. Here's an example based on Scott's schema.

SQL> -- Duplicate jobs within the department
SQL> 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, SALESMAN

SQL>
SQL> -- This won't work - DISTINCT can't be used in LISTAGG
SQL> 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 function


SQL>
SQL> -- So - select distinct jobs first, then apply LISTAGG to it
SQL> 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, SALESMAN

SQL>

这篇关于我如何从listagg删除重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！