LISTAGG在oracle中返回不同的值怎么处理？
LISTAGG在oracle中返回不同的值怎么处理？
提问于 2018-03-21 08:02:26
我正在尝试使用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不同的值。
Stack Overflow用户
发布于 2018-03-21 17:42:38
以下是如何解决问题。
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
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100007716
复制相关文章
相似问题