如何通过独特的过滤器使用 Oracle 的 LISTAGG 函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7355095/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Oracle's LISTAGG function with a unique filter?
提问by daveslab
I have a table like this:
我有一张这样的表：
group_id name
-------- ----
1 David
1 John
1 Alan
1 David
2 Julie
2 Charles
And I want the following result:
我想要以下结果：
group_id names
-------- -----
1 'Alan, David, John'
2 'Charles, Julie'
I can use the following query:
我可以使用以下查询：
select group_id,
listagg(name, ',') within group (order by name) as names
from demotable
group by group_id
To get this (very similar result):
要得到这个（非常相似的结果）：
group_id names
-------- -----
1 'Alan, David, David, John'
2 'Charles, Julie'
Any ideas how I can filter the names by uniqueness in the LISTAGGcall?
任何想法如何通过LISTAGG呼叫中的唯一性过滤名称？
采纳答案by Ollie
I don't have an 11g instance available today but could you not use:
我今天没有可用的 11g 实例，但您不能使用：
SELECT group_id,
LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names
FROM (
SELECT UNIQUE
group_id,
name
FROM demotable
)
GROUP BY group_id
回答by ozmike
Super simple answer - solved!
超级简单的答案 - 解决了！
my full answer hereit is now built in in some oracle versions.
我在这里的完整答案现在内置于某些 oracle 版本中。
select group_id,
regexp_replace(
listagg(name, ',') within group (order by name)
,'([^,]+)(,)*(,|$)', '')
from demotable
group by group_id;
This only works if you specify the delimiter to ',' not ', ' ie works only for no spaces after the comma. If you want spaces after the comma - here is a example how.
这仅适用于将分隔符指定为 ',' 而不是 ', ' 的情况，即仅适用于逗号后没有空格的情况。如果您想要逗号后的空格 - 这里是一个例子。
select
replace(
regexp_replace(
regexp_replace('BBall, BBall, BBall, Football, Ice Hockey ',',\s*',',')
,'([^,]+)(,)*(,|$)', '')
,',',', ')
from dual
gives BBall, Football, Ice Hockey
给 BBall, 足球, 冰球
回答by Michal Satny
create table demotable(group_id number, name varchar2(100));
insert into demotable values(1,'David');
insert into demotable values(1,'John');
insert into demotable values(1,'Alan');
insert into demotable values(1,'David');
insert into demotable values(2,'Julie');
insert into demotable values(2,'Charles');
commit;
select group_id,
(select listagg(column_value, ',') within group (order by column_value) from table(coll_names)) as names
from (
select group_id, collect(distinct name) as coll_names
from demotable
group by group_id
)
GROUP_ID NAMES
1 Alan,David,John
2 Charles,Julie
回答by PJA
select group_id,
listagg(name, ',') within group (order by name) as names
over (partition by group_id)
from demotable
group by group_id
回答by Felypp Oliveira
I needed this peace of code as a subquery with some data filter before aggregation based on the outer most query but I wasn't able to do this using the chosen answer code because this filter should go in the inner most select (third level query) and the filter params was in the outer most select (first level query), which gave me the error ORA-00904: "TB_OUTERMOST"."COL": invalid identifieras the ANSI SQL states that table references (correlation names) are scoped to just one level deep.
在基于最外层查询进行聚合之前，我需要这种代码的和平作为带有一些数据过滤器的子查询，但我无法使用所选的答案代码来执行此操作，因为此过滤器应该进入最内层的选择（第三级查询）并且过滤器参数在最外面的选择（第一级查询）中，这给了我错误ORA-00904：“TB_OUTERMOST”。“COL”：无效标识符，因为 ANSI SQL 指出表引用（相关名称）的范围是只有一层深。
I needed a solution with no levels of subquery and this one below worked great for me:
我需要一个没有子查询级别的解决方案，下面的这个对我来说非常有用：
with
demotable as
(
select 1 group_id, 'David' name from dual union all
select 1 group_id, 'John' name from dual union all
select 1 group_id, 'Alan' name from dual union all
select 1 group_id, 'David' name from dual union all
select 2 group_id, 'Julie' name from dual union all
select 2 group_id, 'Charlie' name from dual
)
select distinct
group_id,
listagg(name, ',') within group (order by name) over (partition by group_id) names
from demotable
-- where any filter I want
group by group_id, name
order by group_id;
回答by zia
below is undocumented and not recomended by oracle. and can not apply in function, show error
以下是未记录的，oracle 不推荐。并且不能在函数中应用，显示错误
select wm_concat(distinct name) as names from demotable group by group_id
regards zia
问候齐亚
回答by marko
In 11g you can use the undocumented function wm_concat like this:
在 11g 中，您可以像这样使用未记录的函数 wm_concat：
select wm_concat(distinct name) as names from demotable group by group_id
