SYS_CONNECT_BY_PATH这个函数是oracle9i才新提出来的!
它一定要和connect by子句合用!
第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!
START WITH 代表你要开始遍历的的节点
它一定要和connect by子句合用!
第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符!
START WITH 代表你要开始遍历的的节点
CONNECT BY PRIOR 是标示父子关系的对应
select deptno,
ltrim(sys_connect_by_path(ename,','),',') emps
from (
select deptno,
ename,
row_number() over
(partition by deptno order by empno) rn,
count(*) over
(partition by deptno) cnt
from emp
)
where level = cnt
start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1;
DEPTNO EMPS
----------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
SQL> select deptno,wmsys.wm_concat(ename) from emp group by deptno;
DEPTNO WMSYS.WM_CONCAT(ENAME)
---------- --------------------------------------------------------------------------------
10 CLARK,MILLER,KING
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
select deptno,
ename,
row_number() over
(partition by deptno order by empno) rn,
count(*) over
(partition by deptno) cnt
from emp;
DEPTNO ENAME RN CNT
---------- ---------- ---------- ----------
10 CLARK 1 3
10 KING 2 3
10 MILLER 3 3
20 SMITH 1 5
20 JONES 2 5
20 SCOTT 3 5
20 ADAMS 4 5
20 FORD 5 5
30 ALLEN 1 6
30 WARD 2 6
30 MARTIN 3 6
30 BLAKE 4 6
30 TURNER 5 6
30 JAMES 6 6
SQL> select ','||'7654,7698,7782,7788'||',' emps from dual;
EMPS
---------------------
,7654,7698,7782,7788,
select substr(emps,instr(emps,',',1,iter.pos)+1,4) from (select ','||'7654,7698,7782,7788'||',' emps
from dual) csv,
(select rownum pos from emp) iter
where iter.pos <=
((length(csv.emps)-length(replace(csv.emps,',')))/length(','))-1;
SUBSTR(E
--------
7654
7698
7782
7788
select substr(emps, instr(emps, ',', 1, 1) + 1, 4),
substr(emps, instr(emps, ',', 1, 2) + 1, 4),
substr(emps, instr(emps, ',', 1, 3) + 1, 4),
substr(emps, instr(emps, ',', 1, 4) + 1, 4)
from (select ',' || '7654,7698,7782,7788' || ',' emps from dual) csv;
SUBS SUBS SUBS SUBS
---- ---- ---- ----
7654 7698 7782 7788