首页 > 代码库 > Oracle 多行合并一行 方法
Oracle 多行合并一行 方法
假如有如下表,其中各个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
执行计划上看,只需要做一次表扫描就可以了,但是这个函数是加密过的,执行计划并不能显示函数内部的操作。
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。