--該測(cè)試腳本可以直接運(yùn)行

--現(xiàn)在想把數(shù)據(jù)庫(kù)中數(shù)據(jù)按照固定字段分組相加，這里總結(jié)了5種方法。

--創(chuàng)建測(cè)試表、添加測(cè)試數(shù)據(jù)

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開始，開發(fā)者可以創(chuàng)建用戶自定義的合計(jì)函數(shù)，除了PL/SQL外，還可以使用任何Oralce所支持的語(yǔ)言（如C＋＋或者Java）來(lái)創(chuàng)建合計(jì)函數(shù)。TYPE頭定義必須包含ODCIAggregateInitialize、ODCIAggregateIterate、ODCIAggregateMerge和ODCIAggregateTerminate這四個(gè)接口函數(shù)。*/

/*Initialize函數(shù)對(duì)數(shù)據(jù)組各個(gè)需要處理的字段各運(yùn)行一次。自然的，我需要為每一個(gè)值準(zhǔn)備一個(gè)新的清單，所以需要初始化持久變量list，這里初始化值為null。*/

/*Iterate函數(shù)處理返回的行，所以實(shí)際上是由它來(lái)創(chuàng)建返回的值的清單。先測(cè)試list是否為空，如果為空，就把list直接設(shè)置為所引入的value值；如果list變量非空，則給list添加一個(gè)逗號(hào)后再插入value值，list的最大允許字符數(shù)32767。*/

/*Terminate函數(shù)在數(shù)據(jù)組的每個(gè)行的感興趣字段數(shù)據(jù)被處理后執(zhí)行。在這個(gè)函數(shù)中我只需簡(jiǎn)單的返回清單變量即可。*/

/*Merge函數(shù)，用來(lái)返回成功標(biāo)記的。*/

/*創(chuàng)建自己的合計(jì)函數(shù)擴(kuò)展了Oracle統(tǒng)計(jì)和文本處理能力。*/

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服務(wù)器沒有配置成支持并行處理的方式，可以去掉參數(shù)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;

--方法四：

--oracle9i以上版本

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;  

方法四的另一種寫法

估計(jì)類似的寫法還有很多，這個(gè)和上一個(gè)不同在于用的沒有帶有start with（filter功能）的connect，并借助level和first_value來(lái)實(shí)現(xiàn)。

SELECT distinct id,ltrim(first_value(mc_add) over (partition by id order BY l DESC),';')   
from (   
SELECT id,LEVEL l,sys_connect_by_path(mc,';') mc_add from   
(   
select id||rownum rn,id||rownum-1 rn_small,id,mc from test   
)   
CONNECT BY PRIOR rn = rn_small   
)   
; 

方法五:

select id,wm_concat(mc) from test group by id