首页 > 代码库 > Oracle 多行合并一行 方法

Oracle 多行合并一行 方法

2024-07-13 23:49:53   223人阅读

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

Sql代码  
  1. SQL> select * from t;  
  2.   
  3.          I A          D  
  4. ---------- ---------- -------------------  
  5.          1 b          2008-03-27 10:55:42  
  6.          1 a          2008-03-27 10:55:46  
  7.          1 d          2008-03-27 10:55:30  
  8.          2 z          2008-03-27 10:55:55  
  9.          2 t          2008-03-27 10:55:59  
  10.   
  11. --- 要获得如下结果，注意字符串需要按照D列的时间排序：  
  12.   
  13. 1  d,b,a  
  14. 2  z,t  



这是一个比较典型的行列转换，有好几种实现方法 

1.自定义函数实现 

Sql代码  
  1. create or replace function my_concat(n number)  
  2. return varchar2  
  3. is  
  4.  type typ_cursor is ref cursor;  
  5.  v_cursor typ_cursor;  
  6.  v_temp varchar2(10);  
  7.  v_result varchar2(4000):= ‘‘;  
  8.  v_sql varchar2(200);  
  9. begin  
  10.  v_sql := ‘select a from t where i=‘ || n ||‘ order by d‘;  
  11.  open v_cursor for v_sql;  
  12.  loop  
  13.     fetch v_cursor into v_temp;  
  14.     exit when v_cursor%notfound;  
  15.     v_result := v_result ||‘,‘ || v_temp;  
  16.  end loop;  
  17.  return substr(v_result,2);  
  18. end;  
  19.   
  20. SQL> select i,my_concat(i) from t group by i;  
  21.   
  22.          I MY_CONCAT(I)  
  23. ---------- --------------------  
  24.          1 d,b,a  
  25.          2 z,t  



虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。 

2.使用sys_connect_by_path 

Sql代码  
  1. select i,ltrim(max(sys_connect_by_path(a,‘,‘)),‘,‘) a  
  2. from  
  3. (  
  4. select i,a,d,min(d) over(partition by i) d_min,  
  5. (row_number() over(order by i,d))+(dense_rank() over (order by i)) numid  
  6. from t  
  7. )  
  8. start with d=d_min connect by numid-1=prior numid  
  9. group by i;  


从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候： 
 
3.使用wm_sys.wm_concat 
这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序: 

Sql代码  
  1. SQL> select i,wmsys.wm_concat(a) from t group by i;  
  2.   
  3.          I WMSYS.WM_CONCAT(A)  
  4. ---------- --------------------  
  5.          1 b,a,d  
  6.          2 z,t  
  7.   
  8. SQL> select i,wmsys.wm_concat(a)  
  9.   2  from  
  10.   3  (select * from t order by i,d)  
  11.   4  group by i;  
  12.   
  13.          I WMSYS.WM_CONCAT(A)  
  14. ---------- --------------------  
  15.          1 d,b,a  
  16.          2 z,t  


执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。
 


联系
我们