如何寫這個sql語句?

lfree發表於2005-05-10
SQL
摘自：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


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-82787/，如需轉載，請註明出處，否則將追究法律責任。

相關文章