将一个字段的多行记录按照另一个字段值进行分组,组合成一行记录。新组合成的一行记录可以使用特定字符如逗号加以区分。
在以前的文档中,我写好一个方法( 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;
(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;
使用over子句将该函数变成分析函数。
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
分享到:
相关推荐
怎么合并多行记录的字符串,一直是oracle新手喜欢问的SQL问题之一,关于这个问题的帖子我看过不下30个了,现在就对这个问题,进行一个总结。
《精通SQ:结构化查询语言详解》全面讲解SQL语言,提供317个典型应用,读者可以随查随用,针对SQL Server和Oracle进行讲解,很有代表性。 全书共包括大小实例317个,突出了速学速查的特色。《精通SQ:结构化查询语言...
11.2 插入多行记录 216 11.2.1 由values关键字引入多行数据插入 217 11.2.2 使用select语句插入值 217 11.3 表中数据的复制 220 11.3.1 基本语法 221 11.3.2 应用实例 221 11.4 从外部数据源导入、导出数据 ...
1.5 SQL开发环境 1.5.1 SQL环境介绍 1.5.2 SQL的层次结构 1.5.3 SQL环境中的对象 1.5.4 SQL环境中的程序系统 1.6 SQL语句基础 1.6.1 SQL常量 1.6.2 SQL表达式 1.6.3 SQL数据类型 1.6.4 注释符 1.6.5 批处理 1.6.6 ...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
SQL语句可以在一行上写出,建议多行写出,便于阅读和调试。 多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号,但特定的DBMS可能必须在单条SQL语句后加分号。 SQL语句的最后一句要以 “;”号...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
第1章 RDBMS基础:SQLServer数据库的构成 1.1 数据库对象概述 1.1.1 数据库对象 1.1.2 事务日志 1.1.3 最基本的数据库对象:表 1.1.4 文件组 1.1.5 数据库关系图 1.1.6 视图 1.1.7 存储过程 1.1.8 用户自定义函数 ...
《SQL Server 2008编程入门经典(第3版)》首先概述了数据库设计的概念,介绍了如何用SQL Setver 2008实现这些基本概念。然后,讲述了RDBMS(关系数据库管理系统)的功能和它在开发系统架构方面的优势。SQL Server ...
《SQL Server 2008编程入门经典(第3版)》首先概述了数据库设计的概念,介绍了如何用SQL Setver 2008实现这些基本概念。然后,讲述了RDBMS(关系数据库管理系统)的功能和它在开发系统架构方面的优势。SQL Server ...
使用聚合函数实现 多行合并 Drop Type Strcat_type; Drop Function f_StrCat; Drop Package UTL_INet; Variable ls_ObjectName VarChar2(128); Begin Select Sequence_Name Into :ls_ObjectName From User_...
理解窗口函数可以从理解聚合函数开始,我们知道聚合函数的概念,就是将某列多行中的值按照聚合规则合并为一行,比如说Sum、AVG等等,简单的概念如图1所示。图1.聚合函数因此,通常来说,聚合后的行数都要小于聚合前...
8.5.2 例1:从前一行中返回一个值 203 8.5.3 理解数据行的位移 204 8.5.4 例2:从下一行中返回一个值 204 8.6 First_value和Last_value 205 8.6.1 例子:使用First_value来计算最大值 206 8.6.2 例子:使用...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
11.8.1 将结果集转置为一行 11.8.2 把结果集转置为多行 11.9 递归查询 11.9.1 Oracle中的CONNECT BY子句 11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数 11.9.3 My SQL Server和DB2中递归查询 附录A 常用...
43 <br>0061 树的实现 44 <br>3.2 排序 48 <br>0062 如何实现选择排序算法 48 <br>0063 如何实现冒泡排序算法 49 <br>0064 如何实现快速排序算法 50 <br>0065 如何实现插入排序算法 ...