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表:
listagg与wm_concat

wm_concat:
select deptno,wmsys.wm_concat(ename || '-' || job) name from emp group by deptno;
结果:
listagg与wm_concat
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;
结果:
listagg与wm_concat