三种字符串分组相加的方法

上一次总结了三种字符串分组相加的方法,参看

http://www.itpub.net/606514.html

从大家的回复中得到第四种方法,所以整理了一下,做为一个续贴吧。

期待第五种、第六种......*/

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;

select id,mc,row_number() over(partition by id order by id) rn_by_id,

row_number() over (order by id) + id rn from test;

/*

利用分析函数,构造两列,做为连接的条件:按照id分组,RN-1等于PRIOR RN作为条件连接。

ID MC RN_BY_ID RN

---------- -------------------------------------------------- ---------- ----------

1 11111 1 2

1 22222 2 3

2 11111 1 5

2 22222 2 6

3 11111 1 8

3 22222 2 9

3 33333 3 10

*/

select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from (

select id,mc,row_number() over(partition by id order by id) rn_by_id,

row_number() over (order by id) + id rn from test

)

start with rn_by_id = 1 connect by rn - 1 = prior rn

group by id

order by id;

/*

另用sys_connect_by_path函数实现字符串的连接,把最左边的分号去掉,即得到我们想要的结果

ID ADD_MC

---------- --------------------------------------------------------------------------------

1 11111;22222

2 11111;22222

3 11111;22222;33333

*/

select * from test;

 

源文档 <http://www.itpub.net/614563.html>

变换一下:(考虑id不是数字的情况)

select id,ltrim(max(sys_connect_by_path(mc,';')),';') from(

select id,mc,row_number() over(partition by id order by id) id1,

row_number() over(order by id) + dense_rank() over(order by id) id2

from test

)

start with id1=1 connect by prior id2 = id2 -1

group by id order by id;

 

源文档 <http://www.itpub.net/614563.html>

 

数据库中分组字符串相加

zhouwf0726 | 27 七月, 2006 12:01

--该测试脚本可以直接运行

--现在想把数据库中数据按照固定字段分组相加,这里总结了三种方法。

--创建测试表、添加测试数据

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;

http://zhouwf0726.itpub.net/post/9689/161638

 

源文档 <http://www.itpub.net/606514.html>

 

 

 
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值