Oracle 多行合并一行方法

最新推荐文章于 2022-09-05 14:41:11 发布

转载最新推荐文章于 2022-09-05 14:41:11 发布 · 1k 阅读

文章标签：

#oracle

Oracle 专栏收录该内容

8 篇文章

订阅专栏

引用自：http://lovejuan1314.iteye.com/blog/413694

OTE:特别声明一下内容转自网络
http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html

假如有如下表，其中各个i值对应的行数是不定的

   Sql代码  
   
  
 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.自定义函数实现

   Sql代码  
   
  
 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

   Sql代码  
   
  
 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代码  
   
  
 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  