表ath lx sl mc bm sj
aaa 0213a408 2 mm aba sjj
bbb 0845a5 5 ll ama sls
aaa 0213b6 4 hh asa srs表b
th lx jf js sx sl mc bm sj
aaa 0213a408 02 13 1 6 mm aba sjj
aaa 0213a408 13 a4 2 2 mm aba sjj
aaa 0213a408 a4 08 3 2 mm aba sjj
bbb 0845a5 08 45 1 5 ll ama sls
bbb 0845a5 45 a5 2 5 ll ama sls
aaa 0213b6 13 b6 2 4 hh asa srs如何把表a 中的 lx 拆分成如表b的结构, 如果 th,jf,js,sx相同的话,还要把 sl 求和 表b中第一条记录的 sl 就是求各得到的。请高手指点的,在pl/sql 中实现
aaa 0213a408 2 mm aba sjj
bbb 0845a5 5 ll ama sls
aaa 0213b6 4 hh asa srs表b
th lx jf js sx sl mc bm sj
aaa 0213a408 02 13 1 6 mm aba sjj
aaa 0213a408 13 a4 2 2 mm aba sjj
aaa 0213a408 a4 08 3 2 mm aba sjj
bbb 0845a5 08 45 1 5 ll ama sls
bbb 0845a5 45 a5 2 5 ll ama sls
aaa 0213b6 13 b6 2 4 hh asa srs如何把表a 中的 lx 拆分成如表b的结构, 如果 th,jf,js,sx相同的话,还要把 sl 求和 表b中第一条记录的 sl 就是求各得到的。请高手指点的,在pl/sql 中实现
select 'aaa' th,'0213a408' lx,2 sl,'mm' mc,'aba' bm,'sjj' sj from dual
union all
select 'bbb' th,'0845a5' lx,5 sl,'ll' mc,'ama' bm,'sls' sj from dual
union all
select 'aaa' th,'0213b6' lx,4 sl,'hh' mc,'asa' bm,'srs' sj from dual
)
,temp as(
select level lv from dual connect by level < 5
)
select th,min(lx) lx,lv1 jf,lv2 js,sx,sum(sl) sl,min(mc) mc,min(bm) bm,min(sj) s from(
select th,lx,lv1,lv2,sx,sl,mc,bm,sj,lv from(
select th,lx,substr(lx,2*lv-1,2) lv1,substr(lx,2*lv+1,2) lv2,lv sx,sl,mc,bm,sj,lv from a,temp order by th,lx,lv
) where lv2 is not null
) group by th,lv1,lv2,sx order by bm,mc