时间戳

首页 > 解决方案 > 如何从 Oracle LISTAGG 聚合查询的结果中消除重复值

sql - 如何从 Oracle LISTAGG 聚合查询的结果中消除重复值

问题描述

我正在尝试根据Oracle 的建议从汇总结果中消除重复值：

with depts as (
select 'ACCOUNTING' as department , 'CLERC' as job from dual union all
select 'ACCOUNTING' as department , 'MANAGER' as job from dual union all
select 'ACCOUNTING' as department , 'PRESIDENT' as job from dual union all

select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'MANAGER' as job from dual union all

select 'SALES' as department , 'MANAGER' as job from dual union all
select 'SALES' as department , 'CLERK' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual
)

select d.department,
       listagg (d.job,', ' ) within group (order by d.job) jobs
  from depts d
 group by d.department

如您所见，外业工作包含重复的价值。

Oracle 建议以这种方式消除这种情况：

with depts as (
select 'ACCOUNTING' as department , 'CLERC' as job from dual union all
select 'ACCOUNTING' as department , 'MANAGER' as job from dual union all
select 'ACCOUNTING' as department , 'PRESIDENT' as job from dual union all

select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'ANALYST' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'CLERK' as job from dual union all
select 'RESEARCH' as department , 'MANAGER' as job from dual union all

select 'SALES' as department , 'MANAGER' as job from dual union all
select 'SALES' as department , 'CLERK' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual union all
select 'SALES' as department , 'SALESMAN' as job from dual
)

SELECT d.department,
       (select LISTAGG(job,', ')
               WITHIN GROUP (ORDER BY job)
          from (select unique job job
                  from depts t
                 where t.department = d.department)) jobs
  FROM depts d

但它不适用于版本。Oracle Database 11g 企业版 11.2.0.4.0 - 64 位。

这个推荐正确吗？据我所知，我们无法将数据发送到嵌套查询到第二级嵌套。

标签： sqlstringoraclegroup-bylistagg

解决方案

从 Oracle 19c 开始，LISTAGG()支持DISTINCT：

select department, listagg(distinct job, ',') within group(order by job) jobs
from depts
group by department

在早期版本中，典型的解决方法是SELECT DISTINCT在子查询中，然后聚合：

select department, listagg(job, ',') within group(order by job) jobs
from (select distinct department, job from depts) t
group by department

DB Fiddle 上的演示：

部门 | 工作                   
:--------- | :------------------------
会计 | 文员、经理、总裁
研究 | 分析师、文员、经理  
销售 | 文员、经理、推销员

推荐阅读