NOTE:特别声明一下内容转自网络
http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html
假如有如下表,其中各个i值对应的行数是不定的
SQL> select * from t;
I A D
---------- ---------- -------------------
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59
--- 要获得如下结果,注意字符串需要按照D列的时间排序:
1 d,b,a
2 z,t
这是一个比较典型的行列转换,有好几种实现方法
1.自定义函数实现
create or replace function my_concat(n number)
return varchar2
is
type typ_cursor is ref cursor;
v_cursor typ_cursor;
v_temp varchar2(10);
v_result varchar2(4000):= '';
v_sql varchar2(200);
begin
v_sql := 'select a from t where i=' || n ||' order by d';
open v_cursor for v_sql;
loop
fetch v_cursor into v_temp;
exit when v_cursor%notfound;
v_result := v_result ||',' || v_temp;
end loop;
return substr(v_result,2);
end;
SQL> select i,my_concat(i) from t group by i;
I MY_CONCAT(I)
---------- --------------------
1 d,b,a
2 z,t
虽然这种方式可以实现需求,但是如果表t的数据量很大,i的值又很多的情况下,因为针对每个i值都要执行一句select,扫描和排序的次数和i的值成正比,性能会非常差。
2.使用sys_connect_by_path
select i,ltrim(max(sys_connect_by_path(a,',')),',') a
from
(
select i,a,d,min(d) over(partition by i) d_min,
(row_number() over(order by i,d))+(dense_rank() over (order by i)) numid
from t
)
start with d=d_min connect by numid-1=prior numid
group by i;
从执行计划上来看,这种方式只需要扫描两次表,比自定义函数的方法,效率要高很多,尤其是表中数据量较大的时候:
3.使用wm_sys.wm_concat
这个函数也可以实现类似的行列转换需求,但是似乎没有办法做到直接根据另外一列排序,所以需要先通过子查询或者临时表排好序:
SQL> select i,wmsys.wm_concat(a) from t group by i;
I WMSYS.WM_CONCAT(A)
---------- --------------------
1 b,a,d
2 z,t
SQL> select i,wmsys.wm_concat(a)
2 from
3 (select * from t order by i,d)
4 group by i;
I WMSYS.WM_CONCAT(A)
---------- --------------------
1 d,b,a
2 z,t
执行计划上看,只需要做一次表扫描就可以了,但是这个函数是加密过的,执行计划并不能显示函数内部的操作。
其他一些方法:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336
分享到:
相关推荐
oracle纯SQL语句多行合并一行,中间用“,”隔开,方便快速的显示在页面上
oracle10g,9i多行合并一行函数
本文实例讲述了oracle实现多行合并的方法。分享给大家供大家参考。具体分析如下: 在写sql时,经常会有将某列的字段合并起来,比如将某人名下每个月的工资列示,但是每个人只能占一行。 像这种场景,可能用行列转换...
SQL语句用with将列分割成多列存为临时表,再将多行某个字段拼接合并为一行
怎么合并多行记录的字符串,一直是oracle新手喜欢问的SQL问题之一,关于这个问题的帖子我看过不下30个了,现在就对这个问题,进行一个总结。
不做详解,直接上图。 SELECT T.NAME, T.TESTSTR FROM TEST2 T; SELECT DISTINCT T.NAME, REPLACE(REGEXP_SUBSTR(T.TESTSTR, '[^,]+', 1, LEVEL), ',', ' ') TESTSTR FROM TEST2 T WHERE 1 = 1 ...
很好的资料 共享给大家 很好研读 增强理解 没事多看看 开发一定没问题
查看用户下所有主键外键引用, Windows环境中Kill掉Oracle线程(orakill),查看有哪些表被锁住,Oracle 官方文档下载,对表收集统计信息,刷新物化视图,Oracle 查看用户权限,Oracle 外连接和 (+)号的用法,多行合并一行
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 例子:使用...
他创办了自己的咨询公司,作为一名独立的顾问,他围绕大量的Oracle技术设计并开发了软件和培训课程,内容涉及数据库、应用服务器和商业智能产品,拥有12年的IT从业经验。 Bob Bryla是Oracle 9i和10g的认证专家,他...
使用聚合函数实现 多行合并 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_...
他创办了自己的咨询公司,作为一名独立的顾问,他围绕大量的Oracle技术设计并开发了软件和培训课程,内容涉及数据库、应用服务器和商业智能产品,拥有12年的IT从业经验。 Bob Bryla是Oracle 9i和10g的认证专家,他...
>如果粘贴一行文字,这行文字将被插入到列模式中所有选中的行 >如果粘贴多行文字,这些文字将被逐行被插入到列模式中选中的行 6. 无限的undo/redo >在十六进制编辑模式和文本模式切换后仍然可以无限次地undo/redo 7....
最近插入一行数据 insert into student(Name,Sex,Birthday,Salary) values('朱文锋','男','01-5月-12',2000); GUID 先创建一个表 create table student2(SNo char(32) primary key,Name nvarchar2(50),Sex ...
9.2 使用集合运算符将多个查询合并为一个查询 9.2.1 UNIONALL运算符 9.2.2 UNION运算符 9.2.3 INTERSECT运算符 9.2.4 MINUS运算符 9.2.5 更复杂的示例 9.3 控制返回行的顺序 9.4 认证小结 9.5 本章 ...
SQL语句可以在一行上写出,建议多行写出,便于阅读和调试。 多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号,但特定的DBMS可能必须在单条SQL语句后加分号。 SQL语句的最后一句要以 “;”号...