<div class=”codetitle”><a style=”CURSOR: pointer” data=”77105″ class=”copybut” id=”copybut77105″ onclick=”doCopy(‘code77105’)”> 代码如下:<div class=”codebody” id=”code77105″>
create table A_TEST
(
PAYOUT_ITEM_CODE VARCHAR2(30) not null,
FORMULA_DET VARCHAR2(1000)
) create table B_TEST
(
ELEMENT_ID VARCHAR2(5) not null,
NAME VARCHAR2(41)
)
FORMULA_DET列里ELEMENT_ID替换成NAME
测试数据如下
<div class=”codetitle”><a style=”CURSOR: pointer” data=”68819″ class=”copybut” id=”copybut68819″ onclick=”doCopy(‘code68819’)”> 代码如下:<div class=”codebody” id=”code68819″>
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30228’,'({30015}+{30016})450′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30102’,'({30015}+{30016})1500′);
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})5000′);
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})2500′);
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})2300′);
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})1150′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30104’,'({30015}+{30016})30012′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30226’,'{30057}2300′);
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})3000′);
insert into a_test (PAYOUT_ITEM_CODE,'{30006}+{30061}+{30008}’);
insert into a_test (PAYOUT_ITEM_CODE,'{30057}380012′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30210’,'({30030}+{30031}+{30032})38000+{30033}23000′);
insert into a_test (PAYOUT_ITEM_CODE,'({30030}+{30031}+{30032}+{30033})38000+{30036}10000′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30229’,'({30015}+{30016})1400′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30216’,'({30015}+{30016})1300′);
insert into a_test (PAYOUT_ITEM_CODE,'({30015}+{30016})650′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30307’,'({30015}+{30016})360′);
insert into a_test (PAYOUT_ITEM_CODE,FORMULA_DET)
values (‘30302′,'{30051}’);
insert into a_test (PAYOUT_ITEM_CODE,'{30052}’);
insert into a_test (PAYOUT_ITEM_CODE,'{30053}’);
insert into a_test (PAYOUT_ITEM_CODE,'{30054}’);
insert into a_test (PAYOUT_ITEM_CODE,'{30055}’);
insert into a_test (PAYOUT_ITEM_CODE,'{30056}’);
insert into a_test (PAYOUT_ITEM_CODE,'{30057}4000′);
insert into a_test (PAYOUT_ITEM_CODE,'{30057}3800′);
insert into a_test (PAYOUT_ITEM_CODE,'{30057}10012′);
insert into a_test (PAYOUT_ITEM_CODE,'{30057}50012′);
insert into a_test (PAYOUT_ITEM_CODE,'{30060}0′);
insert into a_test (PAYOUT_ITEM_CODE,'{30057}/{30057}150000′);
insert into a_test (PAYOUT_ITEM_CODE,'{30057}*6000′);

<div class=”codetitle”><a style=”CURSOR: pointer” data=”46793″ class=”copybut” id=”copybut46793″ onclick=”doCopy(‘code46793’)”> 代码如下:<div class=”codebody” id=”code46793″>
insert into b_test (ELEMENT_ID,NAME)
values (‘30006′,’a1’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30008′,’a2’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30009′,’a3’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30010′,’a4’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30015′,’a5’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30016′,’a6’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30017′,’a7’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30018′,’a8’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30019′,’a9’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30020′,’a10’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30021′,’a11’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30022′,’a12’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30023′,’a13’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30024′,’a14’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30025′,’a15’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30026′,’a16’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30027′,’a17’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30028′,’a18’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30029′,’a19’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30030′,’a20’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30031′,’a21’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30032′,’a22’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30033′,’a23’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30034′,’a24’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30035′,’a25’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30036′,’a26’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30037′,’a27’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30038′,’a28’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30039′,’a29’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30040′,’a30’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30041′,’a31’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30042′,’a32’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30043′,’a33’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30044′,’a34’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30045′,’a35’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30046′,’a36’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30047′,’a37’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30048′,’a38’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30049′,’a39’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30050′,’a40’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30051′,’a41’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30052′,’a42’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30053′,’a43’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30054′,’a44’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30055′,’a45’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30056′,’a46’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30057′,’a47’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30058′,’a48’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30059′,’a49’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30060′,’a50’);
insert into b_test (ELEMENT_ID,NAME)
values (‘30061′,’a51’);

这个如果用function或者是sp做，就没有什么难度了。
但是用sql做就比较难度了
<div class=”codetitle”><a style=”CURSOR: pointer” data=”86314″ class=”copybut” id=”copybut86314″ onclick=”doCopy(‘code86314’)”> 代码如下:<div class=”codebody” id=”code86314″>
select gid,payout_item_code,formula_det,max(substr(txt,1,length(txt)-1)) from (
select a.gid,
a.payout_item_code,
a.formula_det,
replace(sys_connect_by_path(decode(b.element_id,null,a.signal,replace(signal,b.element_id,b.name)),’##’),’##’,”) txt
from
(select gid,row_number() over(partition by gid order by level) rn,
substr(formula_det,decode(rownum-(allcnt-selfcnt),instr(formula_det,’}’,rownum-(allcnt-selfcnt)-1)+1),rownum-(allcnt-selfcnt))-decode(rownum-(allcnt-selfcnt),rownum-(allcnt-selfcnt)-1))) signal
from (select a.payout_item_code,a.rowid gid,
a.formula_det||’}’ formula_det,
length(a.formula_det) –
length(replace(a.formula_det,”)) + 1 selfcnt,
sum(length(a.formula_det) – length(replace(a.formula_det,”))+1) over(order by rowid) allcnt,sum(length(a.formula_det) – length(replace(a.formula_det,”))+1) over() sumcnt
from a_test a) t1
start with (allcnt-selfcnt)=0 connect by rownum < sumcnt+1 and instr(formula_det,rownum-(allcnt-selfcnt)) >0) a
left join b_test b on instr(a.signal||’}’,'{‘||b.element_id||’}’,1)>0
start with a.rn = 1 connect by prior a.gid = a.gid and prior a.rn + 1 = a.rn)
group by gid,formula_det

• 作者: 三十而立
  
• 时间：2009年10月21日 17:09:43
  
• 请尊重原创作品。转载请保持文章完整性,并以超链接形式注明原始作者“inthirties（三十而立）”和出处”http://blog.csdn.net/inthirties/archive/2009/10/21/4706281.aspx”,深入讨论可以联系inthirties@gmail.com。