记得刚毕业的那会,有次有个朋友问了我一个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
表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/