使用 Oracle LISTAGG 函数消除重复项

2021-11-27 00:00:00 sql analytics oracle plsql oracle11gr2

可能的重复:
oracle 中的 LISTAGG 返回不同的值

我正在使用 Oracle LISTAGG 函数，但在我返回的名称列表中，我实际上希望消除重复项并只返回不同的值.

I am using Oracle LISTAGG function but within my list of returned names I actually would like to eliminate duplicates and only return distinct values.

我的查询是这样的:

select a.id,
       a.change_id,
       LISTAGG(b.name, ',') WITHIN GROUP (ORDER BY b.name) AS "Product Name", 
from   table_a a,
       table_b b
where  a.id = 1
and    b.change_id = c.change_id
group by a.id, a.change_id

目前正在返回(只显示一条记录):

At the moment, it is returning (just showing one record):

1    1   NameA, NameA, NameB, NameC, NameD, Name D

我希望返回的是:

1    1   NameA, NameB, NameC, Name D

推荐答案

由于评论中的链接答案没有提供我的解决方案，我还是会发布.

As the linked answers in the comment don't provide my flavor of solution, I'll post it anyway.

我只会使用带有虚拟数据的 table_b 来展示概念，您可以轻松添加您的连接等:

I'll only use table_b with dummy data to show the concept, you can easily add your join etc.:

with table_b as ( -- dummy data
 select 'name'||mod(level,3) name
        ,mod(level,3) id
   from dual
  connect by level < 10
 union all
 select 'name'||mod(level,2) name
        ,mod(level,3) id
   from dual
  connect by level < 10
)
select id
      ,RTRIM (
              XMLAGG (
                      XMLELEMENT (E,XMLATTRIBUTES (name|| ',' AS "Seg")
                      )
                     ORDER BY name ASC
              ).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
              ','
             ) AS "Product Name"
       ,LISTAGG(b.name, ',') WITHIN GROUP (ORDER BY b.name) AS "Product Name with dups"
  from table_b b
group by id;

(想法取自 https://forums.oracle.com/forums/thread.jspa?messageID=9634767&tstart=0#9943367)

相关文章