一、背景
最近在工作中,有个需求是要求在oracle统计查询的时候,将表的某一列的所有值用逗号隔开,去重后合并成一行。于是研究了一下listagg和xmlagg 函数 用来合并数据
以下通过实例说明。
二、方法
1.不去重的两种方法
listagg函数
返回结果为varchar2格式的数据,即拼接后的字符串最大可以保存4000字节的数据,所以大于这个数据的字符串就会报ORA-01489 字符串连接的结果过长的错误。
xmlagg函数
当查询结果过长,拼接的字符串长度过长大于4000字节,我们可以使用这个函数,函数返回结果为CLOB类型,大对象数据类型最大可以存储4GB的数据长度。
用法1:用某符号拼接列中所有值
SELECT
LISTAGG(student_name, ',') WITHIN GROUP(ORDER BY student_name) listagg
FROM
student_info t;
执行结果:
listagg |
王一,陈二,张三,李四 |
用法2:按某列 分组,用指定符号拼接组内列中所有值
//方法1 LISTAGG
SELECT
t.student_name,t.student_sex
LISTAGG(student_name, ',') WITHIN GROUP(ORDER BY student_name)
OVER( PARTITION BY student_sex)
listagg
FROM
student_info t;
//方法2 xmlagg
SELECT
xmlagg(xmlparse(content t.student_name || ',' WELLFORMED) order by t.student_name).getClobval()
listagg
FROM
student_info t;
执行结果:
student_name | student_sex | listagg |
王一 | 女 | 王一,陈二 |
陈二 | 女 | 王一,陈二 |
张三 | 男 | 张三,李四 |
李四 | 男 | 张三,李四 |
2.去重的方法
数据表
创建日期 | 公司代码 | 销售物品 |
20230718085055 | 111 | 苹果 |
20230718085055 | 111 | 香蕉 |
20230718090000 | 111 | 苹果 |
20230718090000 | 112 | 牙刷 |
20230718090000 | 112 | 牙刷 |
若不去重则显示数据为
公司代码 | 销售物品 |
111 | 苹果,香蕉,苹果 |
112 | 牙刷,牙刷 |
有多种去重方法,这边建议先去重再聚合
//distinct去重后,再聚合 拼接
select
t.company_code ,LISTAGG(t.sale_name, ',') within group (
order by t.sale_name) OVER(PARTITION BY t.company_code ) LISTAGG
from
(
select
distinct s.sale_name,
s.company_code
from
sale_info s
) t
执行结果:
company_code | LISTAGG |
111 | 苹果,香蕉 |
112 | 牙刷 |
以上即为本人项目中的处理思路,若有帮助到你,那真的太好了!
若没有望亲喷