ORACLE数据库中分组字符串相加默认分类

引用
--该测试脚本可以直接运行 
--现在想把数据库中数据按照固定字段分组相加，这里总结了三种方法。 
--创建测试表、添加测试数据 
Sql代码 
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;  

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;
--方法一： 
Sql代码 
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;   
/  

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;
/
--方法二： 
Sql代码 
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;   
/  

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统计和文本处理能力。*/ 
Sql代码 
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;   
/  

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*/ 
Sql代码 
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;  

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;
方法四： 
Sql代码 
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;  

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 
*/ 
Sql代码 
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;  

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 
*/ 
Sql代码 
select * from test;  

select * from test;
方法四的另一种写法 
估计类似的写法还有很多，这个和上一个不同在于用的没有带有start with（filter功能）的connect，并借助level和first_value来实现。 
Sql代码 
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 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);
变换一下：（考虑id不是数字的情况） 
Sql代码 
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;