listagg，vmsys.vm_concat与sys_connect_by_path函数

 

listagg与vmsys.vm_concat：可以实现行转成列，并以逗号分开的效果。

区别：listagg是11.2新增的函数，且该函数可以实现组内的排序

--vmsys.vm_concat函数使用 如下所示，按部门进行分组，同一组的在一行中用逗号隔开
SELECT deptno, wmsys.wm_concat(ename) FROM emp GROUP BY deptno;

 listagg函数：

sys_connect_by_path函数：

SELECT sys_connect_by_path(ename, ',')
  FROM (SELECT ename, deptno, rownum rn FROM emp)
 START WITH rn = 1
CONNECT BY rn = rownum;

 

--substr从第二个开始截取，去掉第一个逗号
SELECT substr(sys_connect_by_path(ename, ','), 2)
  FROM (SELECT ename, deptno, rownum rn FROM emp ORDER BY deptno)
 START WITH rn = 1
CONNECT BY rn = rownum;

 

--值截取最大的最后一条记录（与vmsys.vm_concat函数有点不同，不能按某个字段进行group by，而是不断的累积）
SELECT max(substr(sys_connect_by_path(ename, ','), 2))
  FROM (SELECT ename, deptno, rownum rn FROM emp ORDER BY deptno)
 START WITH rn = 1
CONNECT BY rn = rownum;

 

--实现与上述max一样的效果（connect_by_isleaf只取出为叶节点的记录）
SELECT substr(sys_connect_by_path(ename, ','), 2),connect_by_isleaf
  FROM (SELECT ename, deptno, rownum rn FROM emp ORDER BY deptno)
 WHERE connect_by_isleaf = 1
 START WITH rn=1
CONNECT BY prior rn = rn-1;