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
自己开发实现的程序如下:
- 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;
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)是可选子句,加上能将聚合函数变成分析函数。
实现该功能的程序如下:
- SELECT deptno,
- LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
- FROM emp
- GROUP BY deptno;
SELECT deptno,
LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
使用over子句将该函数变成分析函数。
- SELECT deptno,
- ename,
- hiredate,
- LISTAGG(ename, ',') WITHIN GROUP(ORDER BY hiredate) OVER(PARTITION BY deptno) AS employees
- 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