ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何写这个sql语句?

如何写这个sql语句?

原创 Linux操作系统 作者:lfree 时间:2005-05-10 00:00:00 0 删除 编辑
摘自: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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2410
  • 访问量
    6178014