Oracle典型行列转换的几种实现方法

最新推荐文章于 2025-05-23 21:04:26 发布

原创最新推荐文章于 2025-05-23 21:04:26 发布 · 883 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #tsql #function #string #加密

本文介绍了在Oracle数据库中实现行列转换的三种方法：使用自定义函数、利用sys_connect_by_path和wm_sys.wm_concat函数。对比了不同方法的执行效率，并提供了执行计划以展示其性能差异。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

假如有如下表，其中各个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

引用地址：http://www.ningoo.net/html/2008/how_to_do_string_aggregate_on_oracle.html