select deptno, sys_connect_by_path(ename, '+') ename_str from(select deptno, ename, count(empno) over(partition by deptno) c, row_number() over(partition by deptno orderby deptno) r from scott.emp) where c = r start with r =1 connect by (prior r = r -1and prior deptno = deptno)
FROM里面的语句执行结果:
SQL>select deptno, ename, 2count(empno) over(partition by deptno) c, 3 row_number() over(partition by deptno orderby ename) r 4from scott.emp 5/ DEPTNO ENAME C R ------ ---------- ---------- ---------- 10 CLARK 31 10 KING 32 10 MILLER 33 20 ADAMS 51 20 FORD 52 20 JONES 53 20 SCOTT 54 20 SMITH 55 30 ALLEN 61 30 BLAKE 62 30 JAMES 63 30 MARTIN 64 30 TURNER 65 30 WARD 66 14 rows selected
最后执行结果:
SQL>select deptno, sys_connect_by_path(ename, '+') ename_str 2from(select deptno, ename, 3count(empno) over(partition by deptno) c, 4 row_number() over(partition by deptno orderby ename) r 5from scott.emp) 6where c = r 7 start with r =1 8 connect by (prior r = r -1and prior deptno = deptno) 9/ DEPTNO ENAME_STR ------ -------------------------------------------------------------------------------- 10+CLARK+KING+MILLER 20+ADAMS+FORD+JONES+SCOTT+SMITH 30+ALLEN+BLAKE+JAMES+MARTIN+TURNER+WARD