摘自：http://www.itpub.net/96851.html

表dept, emp

要得到如下結(jié)果

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/，如需轉(zhuǎn)載，請注明出處，否則將追究法律責任。

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結(jié)

以上是生活随笔為你收集整理的linux下写sql语言,如何写这个sql语句?的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。