listagg，vmsys.vm_concat与sys_connect_by_path函数

最新推荐文章于 2024-02-01 23:01:02 发布

转载最新推荐文章于 2024-02-01 23:01:02 发布 · 177 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/HyacinthYuan/blog/552183

文章标签：

#python #数据库

本文详细对比了Oracle环境下的两种数据拼接技术：listagg和wmsys.wm_concat，包括它们的使用方法、特点和区别。同时，介绍了sys_connect_by_path函数的使用及其与substr、max和connect_by_isleaf的结合应用，为用户提供了一种高效处理数据的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

WMSYS.WM_CONCAT: 依赖WMSYS 用户，不同oracle环境时可能用不了，返回类型为CLOB，可用substr截取长度后to_char转化为字符类型

LISTAGG : 11g2才提供的函数，不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型，最大长度为4000.

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;