请教一个复杂的oracle查询语句(100分)

johnnywong · 2008-02-26

表A ：纪录如下 网上代理号   收件人  地址         联系电话           编号 H76    胡A 敬龙布艺城         23360313        HH003269905CN H47    吴A 志达         23882238        HH003270316CN H21    叶A 沙头大道140号      13XXX828071        HH003270611CN H49    叶A 沙头大道140号      13XXX828071        HH003270611CN H97    叶A 沙头大道140号      13XXX828071        HH003270611CN H00    叶A 沙头大道140号      13XXX828071        HH003270611CN H73    苏A 晖楼强虹公司       13XXX930094        HH003273768CN H92    苏A 晖楼强虹公司       13XXX930094        HH003273768CN H48    苏A 晖楼强虹公司       13XXX930094          HH003273768CN 根据编号相同的，将对应的'网上代理号'合并，各个‘网上代理号’用'/'隔开，需要的查询结果是：（如下) 网上代理号         收件人地址      联系电话编号 H76          胡A 敬龙布艺城      23360313    HH003269905CN H47          吴A 志达      23882238    HH003270316CN H21/H49/H97/H00     叶A 沙头大道140号   13XXX828071  HH003270611CN H73/H92/H48         苏A 晖楼强虹公司    13XXX930094  HH003273768CN 请问改如何操作？

yanfeng1001 · 2008-02-27

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

power255 · 2008-02-27

先增加一个ColumnToRow行变列函数 --该sql生成了一个名为list的函数。功能是将多行的数据记录转换成一行的记录，各数据用“参数的第一位字符隔开”分隔。 --使用样例：select ColumnToRow(','||Tname) from tab where rownum<10; create or replace type list_agg as object ( total varchar2(4000), static function ODCIAggregateInitialize(sctx IN OUT list_agg ) return number, member function ODCIAggregateIterate(self IN OUT list_agg , value IN varchar2 ) return number, member function ODCIAggregateTerminate(self IN list_agg, returnValue OUT varchar2, flags IN number) return number, member function ODCIAggregateMerge(self IN OUT list_agg, ctx2 IN list_agg) return number ); / create or replace type body list_agg is static function ODCIAggregateInitialize(sctx IN OUT list_agg) return number is begin sctx := list_agg(null); return ODCIConst.Success; end; member function ODCIAggregateIterate(self IN OUT list_agg, value IN varchar2 ) return number is begin self.total := self.total||value; return ODCIConst.Success; end; member function ODCIAggregateTerminate(self IN list_agg, returnValue OUT varchar2, flags IN number) return number is begin returnValue := substr(self.total,2); return ODCIConst.Success; end; member function ODCIAggregateMerge(self IN OUT list_agg, ctx2 IN list_agg) return number is begin self.total := self.total||ctx2.total; return ODCIConst.Success; end; end; / CREATE OR REPLACE FUNCTION ColumnToRow(input varchar2 ) RETURN varchar2 PARALLEL_ENABLE AGGREGATE USING list_agg; / 最后用下面的语句查询 select  收件人,地址,联系电话,编号,ColumnToRow('/'||网上代理号)  from 表名 group by 收件人,地址,联系电话,编号

gojohnnywong · 2008-03-04

to yanfeng1001: 你的方法基本能符合要求了，但还有一个关键问题没有解决，如果ID的值含有字母的时候，运行脚本时提示：ORA-01722: 无效数字 例： ID MC ---------- ------------- H1 11111 H1 22222 H2 11111 H2 22222 H3 11111 H3 22222 H3 33333

请教一个复杂的oracle查询语句(100分)

johnnywong

Unregistered / Unconfirmed

yanfeng1001

Unregistered / Unconfirmed

power255

Unregistered / Unconfirmed

gojohnnywong

Unregistered / Unconfirmed

Similar threads