listagg与wm_concat
当查询较慢时,使用listagg代替wm_concat
WMSYS.WM_CONCAT: 依赖WMSYS 用户,不同oracle环境时可能用不了,返回类型为CLOB,可用substr截取长度后to_char转化为字符类型
LISTAGG : 11g2才提供的函数,不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型,最大长度为4000.
listagg语法:
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]
wm_concat 语法:
vm_concat()
实例:
emp表:
wm_concat:
select deptno,wmsys.wm_concat(ename || '-' || job) name from emp group by deptno;
结果:
listagg:
select distinct deptno,LISTAGG(ename|| '-' || job, ',') within group(order by ename) over(partition by emp.deptno) from emp
或
select deptno, LISTAGG(ename|| '-' || job, ',') within GROup(order by ename) from emp group by deptno;
结果: