Oracle listagg如何去重
1.去重思路:利用listagg会忽略null值的特点
2.上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
但是由于官方不推荐使用 wm_concat ,所以尽量不要用此方法
分类:
Oracle
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤
2022-06-29 JQuery_九大选择器
2022-06-29 jQuery 选择同时包含两个class的元素的实现方法
2021-06-29 开窗函数 SUM() OVER()
2018-06-29 C#操作Sqlite快速入门及相关工具收集
2017-06-29 用sp_lock诊断SQL Sever的死锁问题
2016-06-29 修改CKFinder上传路径