如何使用Oracle的LISTAGG函数和独特的过滤器？

Question

我有一个像这样的表：

group_id  name  
--------  ----
1         David
1         John
1         Alan
1         David
2         Julie
2         Charles

我想要以下结果：

group_id  names
--------  -----
1         'Alan, David, John'
2         'Charles, Julie'

我可以使用以下查询：

select group_id, 
       listagg(name, ',') within group (order by name) as names
from demotable
group by group_id 

要得到这个（非常相似的结果）：

group_id  names
--------  -----
1         'Alan, David, David, John'
2         'Charles, Julie'

如何通过 LISTAGG 中的唯一性过滤名称的任何想法称呼？

Answer 1

我今天没有可用的 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

Answer 2

超级简单的答案 - 解决了！

我的完整答案这里现在在一些oracle版本中内置了。

select group_id, 
regexp_replace(
    listagg(name, ',') within group (order by name)
    ,'([^,]+)(,\1)*(,|$)', '\1\3')
from demotable
group by group_id;  

仅当您将分隔符指定为 ',' 而不是 ', ' 时，这才有效，即
仅适用于逗号后不带空格的情况。如果您想在逗号后添加空格 - 这里是一个示例。

select 
replace(
    regexp_replace(
     regexp_replace('BBall, BBall, BBall, Football, Ice Hockey ',',\s*',',')            
    ,'([^,]+)(,\1)*(,|$)', '\1\3')
,',',', ') 
from dual

给出
篮球、足球、冰球

Answer 3

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

Answer 4

select group_id, 
       listagg(name, ',') within group (order by name) as names
       over (partition by group_id)   
from demotable
group by group_id 

Answer 5

以下内容未记录且不被 Oracle 推荐。
并且不能应用于函数，显示错误

select wm_concat(distinct name) as names from demotable group by group_id

问候
齐亚

Answer 6

在基于最外层查询进行聚合之前，我需要这种和平的代码作为带有一些数据过滤器的子查询，但我无法使用所选的答案代码来执行此操作，因为此过滤器应该进入最内层的选择（第三级查询）并且过滤器参数位于最外层的选择（第一级查询）中，这给了我错误ORA-00904：“TB_OUTERMOST”。“COL”：无效标识符，因为ANSI SQL声明表引用（相关名称）的范围仅限于一层深度。

我需要一种没有子查询级别的解决方案，下面的这个解决方案对我来说非常有用：

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;

Answer 7

在 11g 中，您可以使用未记录的函数 wm_concat，如下所示：

     select wm_concat(distinct name) as names from demotable group by group_id