使用Oracle LISTAGG函数消除重复项 [英] Eliminate duplicates using Oracle LISTAGG function
问题描述
可能重复:
在Oracle中使用LISTAGG返回不同的值
Possible Duplicate:
LISTAGG in oracle to return distinct values
我正在使用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 )
这篇关于使用Oracle LISTAGG函数消除重复项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!