使用Oracle的LISTAGG函数消除重复的内容 | 您所在的位置:网站首页 › listagg去除重复 › 使用Oracle的LISTAGG函数消除重复的内容 |
问题描述
可能的重复: listagg in oracle in oracle返回不同的值 我正在使用oracle listagg函数,但在我的返回名称列表中,我实际上想消除重复项,只返回不同的值. 我的查询是这样的: 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目前正在返回(仅显示一个记录): 1 1 NameA, NameA, NameB, NameC, NameD, Name D我想返回的是: 1 1 NameA, NameB, NameC, Name D 推荐答案当评论中的链接答案没有提供我的解决方案的味道,我还是将其发布. 我只将table_b与虚拟数据一起显示概念,您可以轻松添加加入等. : 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 ) 本文地址:https://www.itbaoku.cn/post/1712750/Eliminate-duplicates-using-Oracle-LISTAGG-function |
CopyRight 2018-2019 实验室设备网 版权所有 |