oracle中字符串合并与拆分⽰例是在oralce⽰例数据库中执⾏
如现有需求要查每个部门中的员⼯
SELECT DEPTNO, ENAME FROM EMP;
这样的结果并不是很直观，我们希望同部门的显⽰⼀⾏记录
------------------------------------------字符串合并-----------------------------------------------------------
with x1 as
(select deptno,
ename,
row_number() over (partition by deptno order by ename) as rn from emp)
--select * from x1;
---⽤sys_connect_by_path合并字符串
select deptno,substr(sys_connect_by_path(ename,','),2)
from x1
where connect_by_isleaf=1
start with rn=1
connect by (prior rn)=rn-1
oracle 字符串转数组and (prior deptno)=deptno
oralce11g可以改为listagg
select deptno,listagg(ename,',')  within group (order by empno) from emp group by deptno;
很多⼈习惯⽤ wm_concat
但是它oracle⼀个未公开的内部函数，不同版本中返回类型也能存在差异（varchar或clob）
select deptno,wm_concat(ename) from emp group by deptno;
-------------------------------------------反操作-------------------------------------------------------------
with x2 as
(select deptno,listagg(ename,',') within group (order by empno) as ename from emp  group by deptno)
select deptno,regexp_substr(ename,'[^,]+',1,level,'i')
from x2
connect by level<=regexp_count(ename,',')+1
and (prior deptno)=deptno
and (prior dbms_random.value()) is not null

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。