`
lovejuan1314
  • 浏览: 336001 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle 多行合并一行 方法

阅读更多
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
分享到:
评论
2 楼 lovebegar 2015-09-22  
很有价值,之前自己写都是第一种,学习了~
1 楼 li_hy2002 2012-08-27  
谢谢博主,项目里正好用到

相关推荐

Global site tag (gtag.js) - Google Analytics