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_table

18c及更早：

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  

      regexp_replace(

    '2,2,2.1,3,3,3,3,4,4' 

     ,'([^,]+)(,\1)*(,|$)', '\1\3')

from dual

回报

2,2.1,3,4

答案（见下面的注释）：

select col1, 

regexp_replace(

    listagg(

     col2 , ',') within group (order by col2)  -- sorted

    ,'([^,]+)(,\1)*(,|$)', '\1\3') )

   from tableX

where rn = 1

group by col1; 

注意：以上内容适用于大多数情况 - 列表应该排序，您可能需要根据您的数据修剪所有尾随和前导空格。

如果你在> 20或大字符串大小的组中有很多项，你可能会遇到oracle字符串大小限制'字符串连接的结果太长'所以在每个组的成员上放一个最大数字。这只有在可以仅列出第一个成员的情况下才有效。如果你有很长的变量字符串，这可能不起作用。你将不得不进行实验。

select col1,

case 

    when count(col2) < 100 then 

       regexp_replace(

        listagg(col2, ',') within group (order by col2)

        ,'([^,]+)(,\1)*(,|$)', '\1\3')

    else

    'Too many entries to list...'

end

from sometable

where rn = 1

group by col1;

另一种解决方案（没那么简单），希望能够避免oracle的字符串大小限制-字符串大小限制为4000感谢这个职位在这里通过user3465996

select col1  ,

    dbms_xmlgen.convert(  -- HTML decode

    dbms_lob.substr( -- limit size to 4000 chars

    ltrim( -- remove leading commas

    REGEXP_REPLACE(REPLACE(

         REPLACE(

           XMLAGG(

             XMLELEMENT("A",col2 )

               ORDER BY col2).getClobVal(),

             '<A>',','),

             '</A>',''),'([^,]+)(,\1)*(,|$)', '\1\3'),

                  ','), -- remove leading XML commas ltrim

                      4000,1) -- limit to 4000 string size

                      , 1)  -- HTML.decode

                       as col2

 from sometable

where rn = 1

group by col1;

一些测试用例 - 仅供参考

regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,\1)+', '\1')

-> 2.1,3,4 Fail

regexp_replace('2 ,2 ,2.1,3 ,3 ,4 ,4 ','([^,]+)(,\1)+', '\1')

-> 2 ,2.1,3,4 Success  - fixed length items

项目中包含的项目，例如。2,21

regexp_replace('2.1,1','([^,]+)(,\1)+', '\1')

-> 2.1 Fail

regexp_replace('2 ,2 ,2.1,1 ,3 ,4 ,4 ','(^|,)(.+)(,\2)+', '\1\2')

-> 2 ,2.1,1 ,3 ,4  -- success - NEW regex

 regexp_replace('a,b,b,b,b,c','(^|,)(.+)(,\2)+', '\1\2')

-> a,b,b,c fail!

v3 - 正则表达式感谢伊戈尔！适用于所有情况。

select  

regexp_replace('2,2,2.1,3,3,4,4','([^,]+)(,\1)*(,|$)', '\1\3') ,

---> 2,2.1,3,4 works

regexp_replace('2.1,1','([^,]+)(,\1)*(,|$)', '\1\3'),

--> 2.1,1 works

regexp_replace('a,b,b,b,b,c','([^,]+)(,\1)*(,|$)', '\1\3')

---> a,b,c works

from dual

Answer 3

你可以使用未记录的wm_concat功能。

select col1, wm_concat(distinct col2) col2_list 
from tab1group by col1;

此函数返回clob列，如果您希望可以使用dbms_lob.substr将clob转换为varchar2。