本文共 3870 字,大约阅读时间需要 12 分钟。
SQL> select * from test;
ID MC
---------- -------------1 111111 222222 111112 222223 111113 222223 33333已选择7行。
SQL> select id,ltrim(max(sys_connect_by_path(mc,',')),',') row2col
from (select id,mc,id+(row_number() over(order by id)) node_id,row_number() over(partition by id order by id) rnfrom test)start with rn = 1connect by node_id-1 = prior node_idgroup by idorder by id;ID ROW2COL
---------- -------------------------------------------------------------1 11111,222222 11111,222223 11111,22222,33333SQL> select id,replace(max(sys_connect_by_path(mc,',')),',') row2col
from (select id,mc,id+(row_number() over(order by id)) node_id,row_number() over(partition by id order by id) rnfrom test)start with rn = 1connect by node_id-1 = prior node_idgroup by idorder by id;ID ROW2COL
---------- ------------------------------------------------------------1 11111222222 11111222223 111112222233333[@more@]--该测试脚本可以直接运行
--现在想把数据库中数据按照固定字段分组相加,这里总结了三种方法。
--创建测试表、添加测试数据
create table test(id varchar2(10),mc varchar2(50));
insert into test values('1','11111');insert into test values('1','22222');insert into test values('2','11111');insert into test values('2','22222');insert into test values('3','11111');insert into test values('3','22222');insert into test values('3','33333');commit;--方法一:
set serveroutput on size 1000000 declare union_mc varchar2(200); begin for cur_a in(select distinct id from test) loop for cur_b in(select mc from test where id=cur_a.id) loop union_mc:=union_mc||cur_b.mc; end loop; dbms_output.put_line(cur_a.id||chr(9)||union_mc); union_mc := ''; end loop; end; /--方法二:
CREATE OR REPLACE function link(v_id varchar2)
return varchar2 is union_mc varchar2(200); begin for cur in (select mc from test where id=v_id) loop union_mc := union_mc||cur.mc; end loop; union_mc := rtrim(union_mc,1); return union_mc; end; /select id,link(id) from test group by id;
--方法三:
/*从Oracle 9i开始,开发者可以创建用户自定义的合计函数,除了PL/SQL外,还可以使用任何Oralce所支持的语言(如C++或者Java)来创建合计函数。TYPE头定义必须包含ODCIAggregateInitialize、ODCIAggregateIterate、ODCIAggregateMerge和ODCIAggregateTerminate这四个接口函数。*/
/*Initialize函数对数据组各个需要处理的字段各运行一次。自然的,我需要为每一个值准备一个新的清单,所以需要初始化持久变量list,这里初始化值为null。*/
/*Iterate函数处理返回的行,所以实际上是由它来创建返回的值的清单。先测试list是否为空,如果为空,就把list直接设置为所引入的value值;如果list变量非空,则给list添加一个逗号后再插入value值,list的最大允许字符数32767。*/
/*Terminate函数在数据组的每个行的感兴趣字段数据被处理后执行。在这个函数中我只需简单的返回清单变量即可。*/
/*Merge函数,用来返回成功标记的。*/
/*创建自己的合计函数扩展了Oracle统计和文本处理能力。*/
create or replace type t_cat as object ( union_mc VARCHAR2(200), static function ODCIAggregateInitialize(sctx IN OUT t_cat) return number, member function ODCIAggregateIterate(self IN OUT t_cat,value IN varchar2) return number, member function ODCIAggregateTerminate(self IN t_cat,returnValue OUT varchar2, flags IN number) return number, member function ODCIAggregateMerge(self IN OUT t_cat,ctx2 IN t_cat) return number );create or replace type body t_cat is
static function ODCIAggregateInitialize(sctx IN OUT t_cat ) return number is begin sctx := t_cat(''); return ODCIConst.Success; end;member function ODCIAggregateIterate(self IN OUT t_cat, value IN varchar2)
return number is begin self.union_mc := self.union_mc || value; return ODCIConst.Success; end;member function ODCIAggregateTerminate(self IN t_cat, returnValue OUT varchar2, flags IN number) return number is
begin returnValue := self.union_mc; return ODCIConst.Success; end;member function ODCIAggregateMerge(self IN OUT t_cat , ctx2 IN t_cat ) return number is
begin return ODCIConst.Success; end; end; //*如果你的Oracle服务器没有配置成支持并行处理的方式,可以去掉参数PARALLEL_ENABLE*/
create or replace function catstr(v_mc varchar2) return varchar2 PARALLEL_ENABLE AGGREGATE USING t_cat;
/select id,catstr(mc) from test group by id;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/219138/viewspace-913429/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/219138/viewspace-913429/