oracle中字符串合并与拆分详解数据库

这样的结果并不是很直观，我们希望同部门的显示一行记录

字符串合并

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 

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

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/4475.html