Oracle中的LISTAGG返回不同的值

Question

Oracle中的LISTAGG返回不同的值

我试图LISTAGG在Oracle中使用该功能。我想只获得该列的不同值。有没有一种方法可以在不创建函数或过程的情况下获得不同的值？

  col1 col2 Created_by
   1 2史密斯 
   1 2约翰 
   1 3 Ajay 
   1 4拉姆 
   1杰克

我需要选择col1和LISTAGGcol2（不考虑第3列）。当我这样做时，我得到这样的结果LISTAGG：[2,2,3,4,5]

我需要在这里删除重复的'2'; 我只需要col2对col1的不同值。

Answer 1

19c及以后：select listagg(distinct the_column, ',') within group (order by the_column)from the_table18c及更早：select listagg(the_column, ',') within group (order by the_column)from (    select distinct the_column     from the_table) t如果您需要更多列，那么您可能正在寻找以下内容：select col1, listagg(col2, ',') within group (order by col2)from (   select col1,           col2,          row_number() over (partition by col1, col2 order by col1) as rn  from foo  order by col1,col2)where rn = 1group by col1;

Answer 2

以下是解决问题的方法。select&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; regexp_replace(&nbsp; &nbsp; '2,2,2.1,3,3,3,3,4,4'&nbsp;&nbsp; &nbsp; &nbsp;,'([^,]+)(,\1)*(,|$)', '\1\3')from dual回报2,2.1,3,4答案（见下面的注释）：select col1,&nbsp;regexp_replace(&nbsp; &nbsp; listagg(&nbsp; &nbsp; &nbsp;col2 , ',') within group (order by col2)&nbsp; -- sorted&nbsp; &nbsp; ,'([^,]+)(,\1)*(,|$)', '\1\3') )&nbsp; &nbsp;from tableXwhere rn = 1group by col1;&nbsp;注意：以上内容适用于大多数情况 - 列表应该排序，您可能需要根据您的数据修剪所有尾随和前导空格。如果你在> 20或大字符串大小的组中有很多项，你可能会遇到oracle字符串大小限制'字符串连接的结果太长'所以在每个组的成员上放一个最大数字。这只有在可以仅列出第一个成员的情况下才有效。如果你有很长的变量字符串，这可能不起作用。你将不得不进行实验。select col1,case&nbsp;&nbsp; &nbsp; when count(col2) < 100 then&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;regexp_replace(&nbsp; &nbsp; &nbsp; &nbsp; listagg(col2, ',') within group (order by col2)&nbsp; &nbsp; &nbsp; &nbsp; ,'([^,]+)(,\1)*(,|$)', '\1\3')&nbsp; &nbsp; else&nbsp; &nbsp; 'Too many entries to list...'endfrom sometablewhere rn = 1group by col1;另一种解决方案（没那么简单），希望能够避免oracle的字符串大小限制-字符串大小限制为4000感谢这个职位在这里通过user3465996select col1&nbsp; ,&nbsp; &nbsp; dbms_xmlgen.convert(&nbsp; -- HTML decode&nbsp; &nbsp; dbms_lob.substr( -- limit size to 4000 chars&nbsp; &nbsp; ltrim( -- remove leading commas&nbsp; &nbsp; REGEXP_REPLACE(REPLACE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;REPLACE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XMLAGG(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;XMLELEMENT("A",col2 )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ORDER BY col2).getClobVal(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'<A>',','),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'</A>',''),'([^,]+)(,\1)*(,|$)', '\1\3'),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ','), -- remove leading XML commas ltrim&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4000,1) -- limit to 4000 string size&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , 1)&nbsp; -- HTML.decode&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;as col2&nbsp;from sometablewhere rn = 1group by col1;一些测试用例 - 仅供参考regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,\1)+', '\1')-> 2.1,3,4 Failregexp_replace('2 ,2 ,2.1,3 ,3 ,4 ,4 ','([^,]+)(,\1)+', '\1')-> 2 ,2.1,3,4 Success&nbsp; - fixed length items项目中包含的项目，例如。2,21regexp_replace('2.1,1','([^,]+)(,\1)+', '\1')-> 2.1 Failregexp_replace('2 ,2 ,2.1,1 ,3 ,4 ,4 ','(^|,)(.+)(,\2)+', '\1\2')-> 2 ,2.1,1 ,3 ,4&nbsp; -- success - NEW regex&nbsp;regexp_replace('a,b,b,b,b,c','(^|,)(.+)(,\2)+', '\1\2')-> a,b,b,c fail!v3 - 正则表达式感谢伊戈尔！适用于所有情况。select&nbsp;&nbsp;regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,\1)*(,|$)', '\1\3') ,---> 2,2.1,3,4 worksregexp_replace('2.1,1','([^,]+)(,\1)*(,|$)', '\1\3'),--> 2.1,1 worksregexp_replace('a,b,b,b,b,c','([^,]+)(,\1)*(,|$)', '\1\3')---> a,b,c worksfrom dual

Answer 3

你可以使用未记录的wm_concat功能。select&nbsp;col1,&nbsp;wm_concat(distinct&nbsp;col2)&nbsp;col2_list&nbsp; from&nbsp;tab1group&nbsp;by&nbsp;col1;此函数返回clob列，如果您希望可以使用dbms_lob.substr将clob转换为varchar2。