資料庫中分組字串相加

SQL> select * from test;

ID MC
---------- -------------
1 11111
1 22222
2 11111
2 22222
3 11111
3 22222
3 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) rn
from test)
start with rn = 1
connect by node_id-1 = prior node_id
group by id
order by id;

ID ROW2COL
---------- -------------------------------------------------------------
1 11111,22222
2 11111,22222
3 11111,22222,33333

SQL> 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) rn
from test)
start with rn = 1
connect by node_id-1 = prior node_id
group by id
order by id;

ID ROW2COL
---------- ------------------------------------------------------------
1 1111122222
2 1111122222
3 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;