示例是在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 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/tech/database/4475.html