LISTAGG在oracle中返回不同的值怎么处理?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (11)

我正在尝试使用LISTAGGOracle中的函数。我想只获得该列的不同值。有没有一种方法可以在不创建函数或过程的情况下获得唯一的值?

  col1 col2 Created_by
   1 2 Smith
   1 2 John 
   1 3 Ajay 
   1 4 Ram 
   1 5 Jack

我需要选择col1和LISTAGGcol2(不考虑第3列)。当我这样做时,我得到如下结果LISTAGG[2,2,3,4,5]

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

提问于
用户回答回答于

以下是如何解决问题。

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.

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
用户回答回答于

你的意思是这样的:

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 = 1
group by col1;

扫码关注云+社区