SQL实现多行记录聚合成一行

http://mikixiyou.iteye.com/blog/1627981

 

将一个字段的多行记录按照另一个字段值进行分组,组合成一行记录。新组合成的一行记录可以使用特定字符如逗号加以区分。

在以前的文档中,我写好一个方法( http://mikixiyou.iteye.com/blog/1489127 )。使用row_number() over 函数。那是在Oracle 10g中使用到的,在11g中提供一个新函数listagg来实现该功能,简单易用。

 


在Oracle 10g中,如果将scott.emp中字段deptno相同的不同ename的记录,从多行记录聚合转换成一行。这是需要自己开发才能完成的。
scott.emp表中原始记录如下:
   DEPTNO ENAME
--------- ----------
       10 CLARK
       10 KING
       10 MILLER
       20 ADAMS
       20 FORD
       20 JONES
      
聚合后的结果如下:

   DEPTNO AGGREGATED_ENAMES
--------- -------------------------
       10 CLARK,KING,MILLER
       20 ADAMS,FORD,JONES

自己开发实现的程序如下:      

 

  1. select deptno, substr(max(sys_connect_by_path(ename, ',')), 2) as employees   
  2.   from (select deptno,   
  3.                ename,   
  4.                row_number() over(partition by deptno order by ename) as rown   
  5.           from emp)   
  6.  start with rown = 1   
  7. connect by prior deptno = deptno   
  8.        and prior rown = rown - 1   
  9.  group by deptno;  
select deptno, substr(max(sys_connect_by_path(ename, ',')), 2) as employees
  from (select deptno,
               ename,
               row_number() over(partition by deptno order by ename) as rown
          from emp)
 start with rown = 1
connect by prior deptno = deptno
       and prior rown = rown - 1
 group by deptno;

 

 

(miki西游  @mikixiyou 原文链接: http://mikixiyou.iteye.com/blog/1627981 )


在11g中,提供了一个新函数 listagg,可以实现这种功能。

listagg的语法如下:

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

WITHIN GROUP 是关键字,必须加。
ORDER BY 也是关键字,必须加。
OVER (PARTITION BY)是可选子句,加上能将聚合函数变成分析函数。

实现该功能的程序如下:

Sql代码 复制代码  收藏代码
  1. SELECT deptno,   
  2.        LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees   
  3.   FROM emp   
  4.  GROUP BY deptno;  
SELECT deptno,
       LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
  FROM emp
 GROUP BY deptno;

 

 
使用over子句将该函数变成分析函数。

Sql代码 复制代码  收藏代码
  1. SELECT deptno,   
  2.        ename,   
  3.        hiredate,   
  4.        LISTAGG(ename, ',') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees   
  5.   FROM emp;  
SELECT deptno,
       ename,
       hiredate,
       LISTAGG(ename, ',') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees
  FROM emp;

 

将每个员工的信息都列出来,最后一项是该员工所在部门所有员工的员工排列,并且是按照雇佣时间排列的。

得到的结果如下:

     DEPTNO ENAME      HIREDATE    EMPLOYEES
--------- ---------- ----------- -------------------------------------
        10 CLARK      09/06/1981  CLARK,KING,MILLER
        10 KING       17/11/1981  CLARK,KING,MILLER
        10 MILLER     23/01/1982  CLARK,KING,MILLER
        20 SMITH      17/12/1980  SMITH,JONES,FORD,SCOTT,ADAMS
        20 JONES      02/04/1981  SMITH,JONES,FORD,SCOTT,ADAMS
        20 FORD       03/12/1981  SMITH,JONES,FORD,SCOTT,ADAMS
        20 SCOTT      19/04/1987  SMITH,JONES,FORD,SCOTT,ADAMS
        20 ADAMS      23/05/1987  SMITH,JONES,FORD,SCOTT,ADAMS
        30 ALLEN      20/02/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 WARD       22/02/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 BLAKE      01/05/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 TURNER     08/09/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 MARTIN     28/09/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
        30 JAMES      03/12/1981  ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES

 

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值