摘自：http://www.itpub.net/96851.html
表dept, emp
要得到如下结果
deptno, dname, employees
---------------------------------
10, accounting, clark;king;miller
20, research, smith;adams;ford;scott;jones
30, sales, allen;blake;martin;james;turners
也就是，每个dept的employee串起来作为一条记录返回。
怎么用一条sql语句完成?
答案如下：
/* Formatted on 2005/05/10 14:58 (Formatter Plus v4.8.5) */
SELECT deptno, dname, emps
FROM (SELECT d.deptno, d.dname,
RTRIM
( e.ename
|| ', '
|| LEAD (e.ename, 1) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 2) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 3) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 4) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 5) OVER (PARTITION BY d.deptno ORDER BY e.ename),
', '
) emps,
ROW_NUMBER () OVER (PARTITION BY d.deptno ORDER BY e.ename) x
FROM scott.emp e, scott.dept d
WHERE d.deptno = e.deptno)
WHERE x = 1
表dept, emp
要得到如下结果
deptno, dname, employees
---------------------------------
10, accounting, clark;king;miller
20, research, smith;adams;ford;scott;jones
30, sales, allen;blake;martin;james;turners
也就是，每个dept的employee串起来作为一条记录返回。
怎么用一条sql语句完成?
答案如下：
/* Formatted on 2005/05/10 14:58 (Formatter Plus v4.8.5) */
SELECT deptno, dname, emps
FROM (SELECT d.deptno, d.dname,
RTRIM
( e.ename
|| ', '
|| LEAD (e.ename, 1) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 2) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 3) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 4) OVER (PARTITION BY d.deptno ORDER BY e.ename)
|| ', '
|| LEAD (e.ename, 5) OVER (PARTITION BY d.deptno ORDER BY e.ename),
', '
) emps,
ROW_NUMBER () OVER (PARTITION BY d.deptno ORDER BY e.ename) x
FROM scott.emp e, scott.dept d
WHERE d.deptno = e.deptno)
WHERE x = 1
