Oracle listagg去重
转载:Oracle listagg去重distinct三种方法总结_每天都要进步一点点的博客-CSDN博客
非常实用的文章,好几次遇到问题都要回来再查一遍,于是转载一下,做个记录
原sql
select t.department_name depname,
t.department_key,
listagg(t.class_key, ',') within group(order by t.class_key) as class_keys
from V_YDXG_TEACHER_KNSRDGL t
where 1 = 1
group by t.department_key, t.department_name
第一种:使用使用wm_concat() + distinct去重聚合
--第一种方法: 使用wm_concat() + distinct去重聚合
select t.department_name depname,
t.department_key,
wm_concat(distinct t.class_key) as class_keys
from V_YDXG_TEACHER_KNSRDGL t
where 1 = 1
group by t.department_key, t.department_name
oracle官方不太推荐使用wm_concat()来进行聚合,能尽量使用listagg就使用listagg。
第二种:使用正则替换方式去重(仅适用于oracle字符串大小比较小的情况)
--第二种方法:使用正则替换方式去重(仅适用于oracle字符串大小比较小的情况)
select t.department_name depname,
t.department_key,
regexp_replace(listagg(t.class_key, ',') within
group(order by t.class_key),
'([^,]+)(,\1)+',
'\1') as class_keys
from V_YDXG_TEACHER_KNSRDGL t
group by t.department_key, t.department_name;
这种方式处理listagg去重问题如果拼接的字符串太长会报oracle超过最大长度的错误,只适用于数据量比较小的场景。
第三种:先去重(子查询),再聚合(推荐使用)
--第三种方法:先去重,再聚合
select t.department_name depname,
t.department_key,
listagg(t.class_key, ',') within group(order by t.class_key) as class_keys
from (select distinct s.class_key, s.department_key, s.department_name
from V_YDXG_TEACHER_KNSRDGL s) t
group by t.department_key, t.department_name
--或者
select s.department_key,
s.department_name,
listagg(s.class_key, ',') within group(order by s.class_key) as class_keys
from (select t.department_key,
t.department_name,
t.class_key,
row_number() over(partition by t.department_key, t.department_name, t.class_key order by t.department_key, t.department_name) as rn
from V_YDXG_TEACHER_KNSRDGL t
order by t.department_key, t.department_name, t.class_key) s
where rn = 1
group by s.department_key, s.department_name;