`
ddandyy
  • 浏览: 215042 次
  • 性别: Icon_minigender_1
  • 来自: 目前上海
社区版块
存档分类
最新评论

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

阅读更多
来自 http://blog.oracle.com.cn/index.php/233877/viewspace-5271.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;


--方法一:


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;


方法四:

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;


方法四的另一种写法

估计类似的写法还有很多,这个和上一个不同在于用的没有带有start with(filter功能)的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);


变换一下:(考虑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;


分享到:
评论

相关推荐

    Oracle 经典试题

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其试题涵盖了众多关键知识点。以下是一些基于题目内容的关键点解析: 1. Oracle默认安装时,账户`SYS`、`SYSTEM`和`SYSMAN`都是解锁的,但`SCOTT`账户通常...

    Oracle函数列表速查

    5. **INSTR()**: 在一个字符串中查找另一个字符串或字符的位置,返回首次出现的位置。如`INSTR('Mississippi', 'i', 3, 3)`返回11,表示从第三个字符开始的第三次'i'出现位置。 #### 处理NULL值的策略 在Oracle中...

    oracle函数列表[定义].pdf

    Oracle数据库中的函数是SQL和PL/SQL编程的重要组成部分,它们帮助开发者处理各种数据类型,包括字符、数字、日期等。本篇文章将详细讲解Oracle中的单行函数和组函数,以及如何在实际应用中有效地使用它们。 单行...

    Oracle中的SUM用法讲解

    如示例中给出的`SumString`函数,它接收几个参数,包括表名、分组列名、结果列名、分组列值和分隔符,然后返回一个由指定列值连接成的字符串。 ```sql CREATE OR REPLACE FUNCTION SumString( i_TableName IN ...

    oracle函数介绍

    Oracle数据库系统中包含丰富的函数,这些函数在数据处理和查询中起着至关重要的作用。以下是关于Oracle函数的详细介绍: 1. **日期运算及函数** - Oracle默认的日期格式是dd MM RR,日期可以直接相加减,例如:`...

    高级Java工程师的经典面试题.doc

    而StringBuffer(或StringBuilder)是可变对象,适合在多线程环境中动态构建字符串,因为它的append()和insert()等方法不会创建新对象,而是直接修改原有对象。如果频繁进行字符串操作,使用StringBuffer(线程安全...

    oracle常用的sql语句

    Oracle数据库是一种关系型数据库管理系统(RDBMS),它使用SQL作为数据访问语言,并提供了强大的数据管理和安全性特性。 #### 三、表 表是数据库中的基本单元,由行和列组成。每一列称为一个字段或属性,每一行称为一...

    java测试题.pdf

    ` 将 x 和 5 相加,结果是一个字符串 "155",最后 `A=parseFloat(str);` 将字符串 "155" 转换为数字 15,然后 `document.write(A);` 将结果输出为 15。 5. 对 location 对象的 href 属性的叙述错误的是 D) 是只读...

    OraclePL/SQL单行函数和组函数详解

    在Oracle数据库中,熟练掌握单行函数和组函数对于编写高效、准确的SQL查询至关重要。理解它们的行为,特别是在处理NULL值时,能帮助你编写出更健壮的数据库应用程序。通过实践和深入学习,你可以更好地利用这些功能...

    Oracle学习(比较齐全的介绍)

    以上介绍的是SQL的基本概念和技术要点,这些是理解和掌握Oracle数据库的关键。 #### 十三、连接查询 **连接查询**用于从两个或多个表中获取数据。 ##### 1. SQL92语法 使用老式语法进行连接: ```sql SELECT * ...

    2009达内SQL学习笔记

    保存在oracle数据库中的所有操作细节: spool oracleday01.txt :开始记录 spool off :开始保存细节 四、SELECT语句:选择操作、投影操作。 select:从一个或多个表中检索一个或多个数据列。包含信息:想选择...

Global site tag (gtag.js) - Google Analytics