SQL实现多行记录聚合成一行
来源:互联网 发布:知乎如何看我的收藏 编辑:程序博客网 时间:2024/06/06 05:31
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 = 1connect 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
- SQL实现多行记录聚合成一行
- 用sql实现两行记录合并为一行记录
- SQL实现多行合并一行
- SQL实现多行合并一行
- SQL中用insert into插入一行或多行记录
- SQL合并多行记录为一行(网抄文章)
- SQL中用insert into插入一行或多行记录
- oracle聚合 将多行单记录合并到一行
- SQL查询多行合并成一行
- sql多行合并成一行
- ORACLE纯SQL实现多行合并一行
- ORACLE纯SQL实现多行合并一行
- 实现多行合并一行的SQL写法
- 实现多行合并一行的SQL-[MSSQL2005]写法
- ORACLE纯SQL实现多行合并一行【转】
- ORACLE纯SQL实现多行合并一行
- ORACLE纯SQL实现多行合并一行
- ORACLE纯SQL实现多行合并一行
- php PHPExcel应用
- PHP非线程安全与线程安全版本的选择技巧
- XMLHelper类 源码(XML文档帮助类,静态方法,实现对XML文档的创建,及节点和属性的增、删、改、查)
- libgdx环境
- 利用预渲染加速iOS设备的图像显示
- SQL实现多行记录聚合成一行
- 图像旋转
- 深入浅出Node.js(一):什么是Node.js
- 函数foorprint足迹和高速缓存
- 使用任务计划程序自动执行任务
- block
- linux下svn命令大全
- Android获取当前时间与星期几
- 从 TeraGrid 中学习到的经验,第 1 部分: 管理大型地理分布的网格