Oraclelistagg去重distinct三种⽅法总结
⾸先还原listagg聚合之后出现重复数据的现象，打开plsql，执⾏如下sql:
1select t.department_name depname,
2        t.department_key,
3        listagg(t.class_key, ',') within group(order by t.class_key) as class_keys
4from V_YDXG_TEACHER_KNSRDGL t
5where1 = 1
6  group by t.department_key, t.department_name
运⾏结果：
如图，listagg聚合之后很多重复数据，下⾯讲解如何解决重复数据问题。
【a】 第⼀种⽅法： 使⽤wm_concat() + distinct去重聚合
1 --第⼀种⽅法：使⽤wm_concat() + distinct去重聚合
2select t.department_name depname,
3        t.department_key,
4        wm_concat(distinct t.class_key) as class_keys
5from V_YDXG_TEACHER_KNSRDGL t
6where1 = 1
7  group by t.department_key, t.department_name
如上图，listagg聚合之后没有出现重复数据了。oracle官⽅不太推荐使⽤wm_concat()来进⾏聚合，能尽量使⽤listagg就使⽤listagg。【b】第⼆种⽅法：使⽤正则替换⽅式去重(仅适⽤于oracle字符串⼤⼩⽐较⼩的情况)
1 --第⼆种⽅法：使⽤正则替换⽅式去重(仅适⽤于oracle字符串⼤⼩⽐较⼩的情况)
2select t.department_name depname,
3        t.department_key,
4        regexp_replace(listagg(t.class_key, ',') within
5                      group(order by t.class_key),
6'([^,]+)(,\1)*(,|$)',
7'\1\3') as class_keys
8from V_YDXG_TEACHER_KNSRDGL t
9  group by t.department_key, t.department_name;
这种⽅式处理listagg去重问题如果拼接的字符串太长会报oracle超过最⼤长度的错误，只适⽤于数据量⽐较⼩的场景。
【c】第三种⽅法：先去重，再聚合(推荐使⽤)
1 --第三种⽅法：先去重，再聚合
select中distinct
2select t.department_name depname,
3        t.department_key,
4        listagg(t.class_key, ',') within group(order by t.class_key) as class_keys
5from (select distinct s.class_key, s.department_key, s.department_name
6from V_YDXG_TEACHER_KNSRDGL s) t
7  group by t.department_key, t.department_name
8
9 --或者
10select s.department_key,
11        s.department_name,
12        listagg(s.class_key, ',') within group(order by s.class_key) as class_keys
13from (select t.department_key,
14                t.department_name,
15                t.class_key,
16                row_number() over(partition by t.department_key, t.department_name, t.class_key order by t.department_key, t.department_name) as rn
17from V_YDXG_TEACHER_KNSRDGL t
18          order by t.department_key, t.department_name, t.class_key) s
19where rn = 1
20  group by s.department_key, s.department_name;
21
推荐使⽤这种⽅式，先把重复数据去重之后再进⾏聚合处理。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。