Oracle技巧集锦

sszxf

more than that：　　SQL> select deptno, dname, emps
　　2 from (
　　3 select d.deptno, d.dname, rtrim(e.ename ||', '||
　　4 lead(e.ename,1) over (partition by d.deptno
　　5 order by e.ename) ||', '||
　　6 lead(e.ename,2) over (partition by d.deptno
　　7 order by e.ename) ||', '||
　　8 lead(e.ename,3) over (partition by d.deptno
　　9 order by e.ename) ||', '||
　　10 lead(e.ename,4) over (partition by d.deptno
　　11 order by e.ename) ||', '||
　　12 lead(e.ename,5) over (partition by d.deptno
　　13 order by e.ename),', ') emps,
　　14 row_number () over (partition by d.deptno
　　15 order by e.ename) x
　　16 from emp e, dept d
　　17 where d.deptno = e.deptno
　　18 )
　　19 where x = 1
　　20 /