LISTAGG函数，因此我尝试使用它。我的问题是我需要消除结果中的重复项，而且似乎无法做到这一点。这里是一个示例。
CREATE TABLE ListAggTest AS (
SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual
CONNECT BY rownum<=6
);
SELECT * FROM ListAggTest;
NUM1 NUM2
---------- ---------------------
1 2
2 2 << Duplicate 2
3 3
4 4
5 5
6 6
我想看的是：
NUM1 NUM2S
---------- --------------------
1 2-3-4-5-6
2 2-3-4-5-6
3 2-3-4-5-6
4 2-3-4-5-6
5 2-3-4-5-6
6 2-3-4-5-6
这是一个接近但不能消除重复的
listagg版本。 > 我有一个解决方案，但是比继续使用自定义聚合函数要糟糕。
#1 楼
在与regexp_replace串联后，可以使用正则表达式和listagg删除重复项：没有。 但是此解决方案确实避免了多次扫描源。
DBFiddle在这里
评论
请注意，要使此REGEX_REPLACE技术可用于删除重复项，重复值在聚合字符串中必须都彼此相邻。
–保达
17年12月12日在21:18
那不是ORDER BY Num2实现的（不是这里）。还是只是想指出您需要ORDER BY才能正常工作？
–杰克·道格拉斯（Jack Douglas）
17年12月13日在8:13
@JackDouglas你能帮我理解这个正则表达式'[[^-] *）（-\ 1）+（$ |-）'和'\ 1 \ 3'，如果我有';'怎么使用？作为分隔符
–马尼·迪普（Mani Deep）
20年8月19日在18:10
#2 楼
据我所知，使用当前可用的语言规范，如果必须通过listagg完成，这是实现所需目标的最短时间。/>您的解决方案比自定义聚合解决方案差吗？
评论
这可行，但是必须进行两次全表扫描。
–雷·里菲尔（Leigh Riffel）
2011年1月19日，0：25
当您有一个需要汇总（小于100000行）的小表时，对于简单的检索而言，性能是可以接受的。在对每种可能的方法进行了近一个小时的测试之后，这是我选择的解决方案！
–马修·杜莫林（Mathieu Dumoulin）
2014年11月19日在12:21
当重复项将中间值超过4000个字符时，此方法也适用。这使其比正则表达式解决方案更安全。
–戈登·利诺夫（Gordon Linoff）
15年10月16日在21:17
#3 楼
尽管这是一个老旧且答案可以接受的帖子，但我认为LAG（）分析函数在这种情况下效果很好，并且值得注意：LAG（）使用花费最少
不需要非平凡的正则表达式来过滤结果
只需进行一次全表扫描（简单示例表上的cost = 4）
这里是建议的代码：
with nums as (
SELECT
num1,
num2,
decode( lag(num2) over (partition by null order by num2), --get last num2, if any
--if last num2 is same as this num2, then make it null
num2, null,
num2) newnum2
FROM ListAggTest
)
select
num1,
--listagg ignores NULL values, so duplicates are ignored
listagg( newnum2,'-') WITHIN GROUP (ORDER BY Num2) OVER () num2s
from nums;
以下结果似乎是OP想要的：
NUM1 NUM2S
1 2-3-4-5-6
2 2-3-4-5-6
3 2-3-4-5-6
4 2-3-4-5-6
5 2-3-4-5-6
6 2-3-4-5-6
#4 楼
创建一个自定义的聚合函数来执行此操作。Oracle数据库提供了许多预定义的聚合函数，例如MAX，MIN，SUM，用于对一组记录。
这些预定义的聚合函数只能与标量
数据一起使用。但是，您可以为这些
函数创建自己的自定义实现，或定义全新的聚合函数，以与
复杂数据配合使用，例如，与使用object
类型存储的多媒体数据，不透明类型和LOB。
SQL DML语句中使用用户定义的聚合函数，就像Oracle数据库内置聚合一样。在服务器上注册了此类函数后，数据库将仅调用您提供的聚集例程，而不是本机函数。
用户定义的聚集可与标量一起使用数据。例如，可能有必要实现特殊的汇总功能，以处理与金融或科学应用相关的复杂统计数据。
用户定义的汇总是可扩展性框架的功能。
您可以使用ODCIAggregate接口例程来实现它们。
#5 楼
这是我对问题的解决方案，我认为它不如使用我们已经存在的自定义聚合函数好。SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s FROM (
SELECT Num1, DECODE(ROW_NUMBER() OVER (PARTITION BY Num2 ORDER BY NULL),
1,Num2,NULL) Num2 FROM ListAggTest
);
#6 楼
请改用WMSYS.WM_Concat。请参阅https://forums.oracle.com/forums/message.jspa?messageID=4372641#4372641。评论
如果您致电Oracle支持部门，并且正在使用wm_concat（即使您认为wm_concat本身不是问题的根源），他们也会有理由拒绝提供帮助，因为该文件没有记录且不受支持-如果使用自定义聚合或任何其他受支持的功能。
–杰克·道格拉斯（Jack Douglas）
2011年11月25日19:23
#7 楼
您还可以使用collect语句，然后编写一个自定义的pl / sql函数，该函数将集合转换为字符串。子句，但如果将
distinct组合在一起，则从11.2.0.2开始不起作用:( 解决方法可能是子选择：
评论
我看不到自定义的pl / sql函数比自定义的聚合函数更好。对于后者而言，生成的SQL当然更简单。由于此问题在11.2.0.2上，因此subselect将添加一次我尝试避免的其他扫描。
–雷·里菲尔（Leigh Riffel）
2011年9月16日在12:13
我想说一个称为ONCE的PL / SQL函数将集合转换为字符串可能比称为数千次的聚合函数更好。我认为这将大大减少上下文切换。
–尼科
2011年9月20日上午11:11
您的理论听起来不错，这也是我尝试避免使用自定义聚合函数并偏爱诸如LISTAGG的内置聚合函数的原因之一。如果您想进行一些时序比较，我会对结果感兴趣。
–雷·里菲尔（Leigh Riffel）
2011年9月20日在12:20
#8 楼
我在遇到ListAgg之前就创建了此解决方案，但是仍然存在一些情况，例如重复值问题，那么此工具很有用。以下版本具有4个参数，可让您控制结果。
解释
CLOBlist将构造函数CLOBlistParam作为参数。
CLOBlistParam具有4个参数
string VARCHAR2(4000) - The variable to be aggregated
delimiter VARCHAR2(100) - The delimiting string
initiator VARCHAR2(100) - An initial string added before the first value only.
no_dup VARCHAR2(1) - A flag. Duplicates are suppressed if this is Y
示例用法
--vertical list of comma separated values, no duplicates.
SELECT CLOBlist(CLOBlistParam(column_name,chr(10)||',','','Y')) FROM user_tab_columns
--simple csv
SELECT CLOBlist(CLOBlistParam(table_name,',','','N')) FROM user_tables
链接到Gist在下面。
https://gist.github.com/peter-genesys/d203bfb3d88d5a5664a86ea6ee34eeca]1
-- Program : CLOBlist
-- Name : CLOB list
-- Author : Peter Burgess
-- Purpose : CLOB list aggregation function for SQL
-- RETURNS CLOB - to allow for more than 4000 chars to be returned by SQL
-- NEW type CLOBlistParam - allows for definition of the delimiter, and initiator of sequence
------------------------------------------------------------------
--This is an aggregating function for use in SQL.
--It takes the argument and creates a comma delimited list of each instance.
WHENEVER SQLERROR CONTINUE
DROP TYPE CLOBlistImpl;
WHENEVER SQLERROR EXIT FAILURE ROLLBACK
create or replace type CLOBlistParam as object(
string VARCHAR2(4000)
,delimiter VARCHAR2(100)
,initiator VARCHAR2(100)
,no_dup VARCHAR2(1) )
/
show error
--Creating CLOBlist()
--Implement the type CLOBlistImpl to contain the ODCIAggregate routines.
create or replace type CLOBlistImpl as object
(
g_list CLOB, -- progressive concatenation
static function ODCIAggregateInitialize(sctx IN OUT CLOBlistImpl)
return number,
member function ODCIAggregateIterate(self IN OUT CLOBlistImpl
, value IN CLOBlistParam) return number,
member function ODCIAggregateTerminate(self IN CLOBlistImpl
, returnValue OUT CLOB
, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT CLOBlistImpl
, ctx2 IN CLOBlistImpl) return number
)
/
show error
--Implement the type body for CLOBlistImpl.
create or replace type body CLOBlistImpl is
static function ODCIAggregateInitialize(sctx IN OUT CLOBlistImpl)
return number is
begin
sctx := CLOBlistImpl(TO_CHAR(NULL));
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT CLOBlistImpl
, value IN CLOBlistParam) return number is
begin
IF self.g_list IS NULL THEN
self.g_list := value.initiator||value.string;
ELSIF value.no_dup = 'Y' AND
value.delimiter||self.g_list||value.delimiter LIKE '%'||value.delimiter||value.string||value.delimiter||'%'
THEN
--Do not include duplicate value
NULL;
ELSE
self.g_list := self.g_list||value.delimiter||value.string;
END IF;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN CLOBlistImpl
, returnValue OUT CLOB
, flags IN number) return number is
begin
returnValue := self.g_list;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT CLOBlistImpl
, ctx2 IN CLOBlistImpl) return number is
begin
self.g_list := LTRIM( self.g_list||','||ctx2.g_list,',');
return ODCIConst.Success;
end;
end;
/
show error
--Using CLOBlist() to create a vertical list of comma separated values
-- SELECT CLOBlist(CLOBlistParam(product_code,chr(10)||',','','Y'))
-- FROM account
--DROP FUNCTION CLOBlist
--/
PROMPT Create the user-defined aggregate.
CREATE OR REPLACE FUNCTION CLOBlist (input CLOBlistParam) RETURN CLOB
PARALLEL_ENABLE AGGREGATE USING CLOBlistImpl;
/
show error
#9 楼
我知道它是在原始帖子发布之后的某个时间，但这是我在谷歌搜索找到相同问题的答案后发现的第一处，并认为落在这里的其他人可能很乐意找到一个简洁的答案，该答案不依赖过于复杂的查询或正则表达式。这将为您提供所需的结果：
with nums as (
select distinct num2 distinct_nums
from listaggtest
order by num2
) select num1,
(select listagg(distinct_nums, '-') within group (order by 1) from nums) nums2list
from listaggtest;
#10 楼
我的想法是实现这样的存储功能：CREATE TYPE LISTAGG_DISTINCT_PARAMS AS OBJECT (ELEMENTO VARCHAR2(2000), SEPARATORE VARCHAR2(10));
CREATE TYPE T_LISTA_ELEMENTI AS TABLE OF VARCHAR2(2000);
CREATE TYPE T_LISTAGG_DISTINCT AS OBJECT (
LISTA_ELEMENTI T_LISTA_ELEMENTI,
SEPARATORE VARCHAR2(10),
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT)
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEITERATE (SELF IN OUT T_LISTAGG_DISTINCT,
VALUE IN LISTAGG_DISTINCT_PARAMS )
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATETERMINATE (SELF IN T_LISTAGG_DISTINCT,
RETURN_VALUE OUT VARCHAR2,
FLAGS IN NUMBER )
RETURN NUMBER,
MEMBER FUNCTION ODCIAGGREGATEMERGE (SELF IN OUT T_LISTAGG_DISTINCT,
CTX2 IN T_LISTAGG_DISTINCT )
RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY T_LISTAGG_DISTINCT IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
SCTX := T_LISTAGG_DISTINCT(T_LISTA_ELEMENTI() , ',');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT T_LISTAGG_DISTINCT, VALUE IN LISTAGG_DISTINCT_PARAMS) RETURN NUMBER IS
BEGIN
IF VALUE.ELEMENTO IS NOT NULL THEN
SELF.LISTA_ELEMENTI.EXTEND;
SELF.LISTA_ELEMENTI(SELF.LISTA_ELEMENTI.LAST) := TO_CHAR(VALUE.ELEMENTO);
SELF.LISTA_ELEMENTI:= SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;
SELF.SEPARATORE := VALUE.SEPARATORE;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN T_LISTAGG_DISTINCT, RETURN_VALUE OUT VARCHAR2, FLAGS IN NUMBER) RETURN NUMBER IS
STRINGA_OUTPUT CLOB:='';
LISTA_OUTPUT T_LISTA_ELEMENTI;
TERMINATORE VARCHAR2(3):='...';
LUNGHEZZA_MAX NUMBER:=4000;
BEGIN
IF SELF.LISTA_ELEMENTI.EXISTS(1) THEN -- se esiste almeno un elemento nella lista
-- inizializza una nuova lista di appoggio
LISTA_OUTPUT := T_LISTA_ELEMENTI();
-- riversamento dei soli elementi in DISTINCT
LISTA_OUTPUT := SELF.LISTA_ELEMENTI MULTISET UNION DISTINCT SELF.LISTA_ELEMENTI;
-- ordinamento degli elementi
SELECT CAST(MULTISET(SELECT * FROM TABLE(LISTA_OUTPUT) ORDER BY 1 ) AS T_LISTA_ELEMENTI ) INTO LISTA_OUTPUT FROM DUAL;
-- concatenazione in una stringa
FOR I IN LISTA_OUTPUT.FIRST .. LISTA_OUTPUT.LAST - 1
LOOP
STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(I) || SELF.SEPARATORE;
END LOOP;
STRINGA_OUTPUT := STRINGA_OUTPUT || LISTA_OUTPUT(LISTA_OUTPUT.LAST);
-- se la stringa supera la dimensione massima impostata, tronca e termina con un terminatore
IF LENGTH(STRINGA_OUTPUT) > LUNGHEZZA_MAX THEN
RETURN_VALUE := SUBSTR(STRINGA_OUTPUT, 0, LUNGHEZZA_MAX - LENGTH(TERMINATORE)) || TERMINATORE;
ELSE
RETURN_VALUE:=STRINGA_OUTPUT;
END IF;
ELSE -- se non esiste nessun elemento, restituisci NULL
RETURN_VALUE := NULL;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT T_LISTAGG_DISTINCT, CTX2 IN T_LISTAGG_DISTINCT) RETURN NUMBER IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;
END; -- fine corpo
CREATE
FUNCTION LISTAGG_DISTINCT (INPUT LISTAGG_DISTINCT_PARAMS) RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING T_LISTAGG_DISTINCT;
// Example
SELECT LISTAGG_DISTINCT(LISTAGG_DISTINCT_PARAMS(OWNER, ', ')) AS LISTA_OWNER
FROM SYS.ALL_OBJECTS;
很抱歉，但是在某些情况下（对于很大的集合），Oracle可能返回此错误：
Object or Collection value was too large. The size of the value
might have exceeded 30k in a SORT context, or the size might be
too big for available memory.
，但我认为这是一个不错的起点；）
评论
请注意，OP已经拥有自己的自定义LISTAGG函数。他们明确地试图查看是否可以找到使用版本11.2内置的LISTAGG函数执行此操作的有效方法。
–RDFozz
18年2月27日在16:12
#11 楼
尝试以下一种方法：select num1,listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) Num2s
from (
select distinct num1
,b.num2
from listaggtest a
,(
select num2
from listaggtest
) b
order by 1,2
)
group by num1
其他可能解决方案的问题是，第1列和第2列的结果之间没有相关性。创建此关联，然后从该结果集中删除重复项。当您执行listagg时，结果集已经干净了。问题更多与以可用格式获取数据有关。
评论
您可能需要添加一些有关其工作原理的说明。
– jkavalik
15-10-28在20:53
感谢您的回答，欢迎访问该网站。如果您可以描述它为什么起作用以及如何起作用，那么它可能会更加有用。
–汤姆五世
15年10月28日在21:09
我一直在尝试更新答案，但始终会出错。 ---其他可能解决方案的问题是，第1列和第2列的结果之间没有关联。要解决此问题，内部查询会创建此关联，然后从该结果集中删除重复项。当您执行listagg时，结果集已经干净了。问题更多与以可用格式获取数据有关。
–凯文
15-10-28在21:21
#12 楼
SQL被设计为简单的语言，非常接近英语。那为什么不用英语写呢？消除num2上的重复项，并使用listagg作为聚合函数-不进行分析，以计算字符串上的concat br />
q4312078q 评论
感谢您的答复。此解决方案需要两次全表扫描，但更重要的是不会返回正确的结果。
–雷·里菲尔（Leigh Riffel）
2011-2-10 14:09
抱歉，我粘贴了一些较旧且错误的版本。
–Štefan Oravec
2011-2-18在0:26
#13 楼
SELECT Num1, listagg(Num2,'-') WITHIN GROUP
(ORDER BY num1) OVER () Num2s FROM
(select distinct num1 from listAggTest) a,
(select distinct num2 from ListAggTest) b
where num1=num2(+);
评论
对于给定的数据，这确实返回了正确的结果，但是假设不正确。 Num1和Num2不相关。 Num1也可能是包含值a，e，i，o，u，y的Char1。这种解决方案无可辩驳，需要对表进行两次完整扫描，从而破坏了使用聚合函数的全部目的。如果解决方案允许进行两次表扫描，则将是首选方法（对于示例数据，其成本比其他任何方法都要低）。 SELECT Num1，（从组中选择LISTAGG（Num2）在（从Num2排序的FROM中）（从listAggTest选择不同的Num2）
–雷·里菲尔（Leigh Riffel）
13年4月9日在12:46
#14 楼
最有效的解决方案是使用GROUP BY进行内部SELECT，因为DISTINCT和正则表达式的运行速度极慢。num1和num2的值（内部SELECT），然后得到按num1分组的所有num2的字符串。
评论
该查询不返回请求的结果。它返回与SELECT * FROM ListAggTest;相同的结果。
–雷·里菲尔（Leigh Riffel）
2013年12月9日14:02
在辩护中，他可能是从另一个stackoverflow问题指出此解决方案的，该问题被标记为该解决方案确实可以解决。那就是我想要的解决方案。似乎我不得不做出不同的假设来发表自己的看法，因此，我将在本问题上留下评论。
– Gerard ONeill
18 Mar 17 '18 at 0:21
评论
应该由null排序还是由Num2排序还是让我感到困惑？@Jack-对重复消除没有影响。根据您的用途，可能是理想的。
LISTAGG的叹息仍然不及Tom Kyte的STRAGG，这跟STRAGG（DISTINCT ...）一样简单
终于有可能：LISTAGG DISTINCT