oracle listagg如何去重

最新推荐文章于 2025-08-26 15:25:06 发布

原创最新推荐文章于 2025-08-26 15:25:06 发布 · 1.3w 阅读

13 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql

Oracle 专栏收录该内容

22 篇文章

订阅专栏

本文介绍了在SQL中如何利用LISTAGG函数进行分组去重与数据聚合，详细解析了示例中的SQL查询过程。同时，对比了不推荐使用的WM_CONCAT方法，并提供了两种方法的查询结果。对于多列去重的情况，文中给出了具体的处理步骤和示例。总结了在处理数据去重时的注意事项和最佳实践。

listagg去重

去重思路：利用listagg会忽略null值的特点
按ENTITY_GROUP_RRN 分组，用 listagg 分别合并 EQPT_ID 与 STATION_ID ，同时要求去重
表 T_TEST 数据如下：

EQPT_ID	ENTITY_GROUP_RRN	STATION_ID
TOOL-001	10493721	JITAI-1
TOOL-003	10493721	JITAI-1
TEST	10493721	S1
TEST	10493721	S2
TEST2	10493721	S1
TEST2	10493721	S2
TOOL-001	12345	JITAI-1
TOOL-003	12345	JITAI-1
TEST	12345	S1
TEST	12345	S2
TEST2	12345	S1
TEST2	12345	S2

3、上SQL：

with T_TEST as
 (
  select 'TOOL-001' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union all
  select 'TOOL-003' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union all
  select 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union all
  select 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union all
  select 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union all
  select 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union all
  select 'TOOL-001' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union all
  select 'TOOL-003' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union all
  select 'TEST' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union all
  select 'TEST' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union all
  select 'TEST2' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union all
  select 'TEST2' EQPT_ID, 123456 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual 
)
select t.entity_group_rrn,
       listagg(decode(rne, 1, t.eqpt_id, null), ',') within group(order by t.eqpt_id) eqpt_ids,
       listagg(decode(rns, 1, t.station_id, null), ',') within group(order by t.station_id) station_ids
  from (select row_number() over(partition by t.entity_group_rrn,t.eqpt_id order by rownum) rne,
               row_number() over(partition by t.entity_group_rrn,t.station_id order by rownum) rns,
               t.*
          from T_TEST t) t
 group by t.entity_group_rrn;

查询结果

总结
1、通常情况可以先分组去重，再合并即可，除非遇到同一分组中，多列分别有重复值，才需要上面那样处理
2、还有另一种去重办法，使用 wm_concat + distinct ，如下：

with T_TEST as
 (select 'TOOL-001' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union all
  select 'TOOL-003' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'JITAI-1' STATION_ID from dual union all
  select 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union all
  select 'TEST' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual union all
  select 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S1' STATION_ID from dual union all
  select 'TEST2' EQPT_ID, 10493721 ENTITY_GROUP_RRN, 'S2' STATION_ID from dual 
)
select wm_concat(distinct t.EQPT_ID) ids,entity_group_rrn, wm_concat(distinct t.station_id) ids2
from T_TEST t  group by t.entity_group_rrn