数字替换变量求结果

记得刚毕业的那会，有次有个朋友问了我一个sql，当时没写出来，需求是这样的
表t1：
ID TYP
1  ax-b+c
2  a-c+bd
表t2：
ID COL VAL
1 ax    15
1 b     2
1 c     33
2 a     123
2 bd   4
2 c     987


需求就是，T1表的typ中每一个元素，在t2表都有都有对应的col和val
根据t2中的val，计算t1公式中的结果：


这个后来想想可以当作面试题了，因为既用到了递归，又用到了行转列，还有基础的左连接，和小的转换处理

今天午休没睡着写了一下：

with t1 as (select 1 id,'ax-b+c' typ from dual union all
select 2 id,'a-c+bd' from dual),
t2 as (select 1 id,'ax' col,15 val from dual union all
select 1 id,'b' col,2 val from dual union all
select 1 id,'c' col,33 val from dual union all
select 2 id,'a' col,123 val from dual union all
select 2 id,'bd' col,4 val from dual union all
select 2 id,'c' col,987 val from dual ),
t3 as (select * from (
select id,regexp_substr(typ,'[^+-]+',1,level) col,2*level-1 l  from t1
connect by level<=regexp_count(typ,'[+-]')+1
and  typ = prior typ
and prior dbms_random.value is not null
union all
select id,regexp_substr(typ,'[+-]+',1,level),2*level l  from t1
connect by level<=regexp_count(typ,'[+-]')
and  typ = prior typ
and prior dbms_random.value is not null
) order by 1,3)
select t3.id,dbms_aw.eval_number(listagg(nvl(to_char(t2.val),t3.col),'') within group (order by t3.l)) from t3 left join t2
on t3.id = t2.id
and t3.col = t2.col
group by t3.id


ID DBMS_AW.EVAL_NUMBER(LISTAGG(NV
1 46
2 -860

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30123160/viewspace-2155719/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30123160/viewspace-2155719/