資料庫中分組字串相加

--該測試指令碼可以直接運行

--現在想把資料庫中資料按照固定欄位分組相加，這裡總結了5種方法。

--建立測試表、添加測試資料

 

  
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;

--方法四：

--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;  
 

方法四的另一種寫法

估計類似的寫法還有很多，這個和上一個不同在於用的沒有帶有start withfilter功能）的connect，並藉助level和first_value來實現。

 

  
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