关于ORACLE报表开发问题，求大神帮忙解答
现在目前数据如下：
with T1 as (
select 'KCP001' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP001' cpid,'B' zjlx,'KB10' zjid from dual union all
select 'KCP002' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP002' cpid,'B' zjlx,'KB20' zjid from dual union all
select 'KCP003' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP003' cpid,'B' zjlx,'KB40' zjid from dual union all
select 'KCP004' cpid,'A' zjlx,'KA40' zjid from dual union all
select 'KCP004' cpid,'B' zjlx,'KB30' zjid from dual union all
select 'KCP005' cpid,'A' zjlx,'KA30' zjid from dual union all
select 'KCP005' cpid,'B' zjlx,'KB30' zjid from dual
), T2 as(
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
)
想得到结果如下：
CPID ZJLX ZJID ZJSL PTCPSL BPTCPSL
1 KCP001 A KA10 50 50 0
2 KCP001 B KB10 50 50 0
3 KCP002 A KA10 0 0 0
4 KCP002 B KB20 20 0 20
5 KCP003 A KA10 0 0 0
6 KCP003 B KB40 0 0 0
7 KCP004 A KA40 10 10 0
8 KCP004 B KB30 60 10 50
9 KCP005 A KA30 0 0 0
10 KCP005 B KB30 0 0 0
其中，KCP001指成品，KA10代表组件，KB10也是组件，KA10和KB10组成KCP001.
当KA10为50，KB10为50,可以组合成50个KCP001。
CPID 成品ID ZJLX 组件类型 ZJID 组件ID ZJSL 组件数量 PTCPSL 组合后的数量 BPTCPSL 不匹配的数量
1、说一下我自己的理解吧，感觉楼主给的数据貌似有点问题，或许也是我没看明白什么意思。瞎说一下吧
2、自己写sql查询一下后是这样的结果
3、附上sql语句
select cpid,zjlx,zjid,zjsl
from(
select t1.cpid,t1.zjlx,t1.zjid,t2.sl zjsl from (
select 'KCP001' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP001' cpid,'B' zjlx,'KB10' zjid from dual union all
select 'KCP002' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP002' cpid,'B' zjlx,'KB20' zjid from dual union all
select 'KCP003' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP003' cpid,'B' zjlx,'KB40' zjid from dual union all
select 'KCP004' cpid,'A' zjlx,'KA40' zjid from dual union all
select 'KCP004' cpid,'B' zjlx,'KB30' zjid from dual union all
select 'KCP005' cpid,'A' zjlx,'KA30' zjid from dual union all
select 'KCP005' cpid,'B' zjlx,'KB30' zjid from dual
)t1 left join (
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
)t2 on t1.zjid=t2.zjid order by cpid asc,zjlx asc
)
这个执行完，查询出来的就是这几个字段： CPID ZJLX ZJID ZJSL(但是有个问题，没有值得地方默认为空了，要是0就好了）
5、然后再去查询PTCPSL(组合后的数量)
组合后的数量是有ZJID ZJSL这两个字段解决的，如果ZJID是“KA10”，就去查询对应的“KB10”的数量，然后PTCPSL的值取两者中的最小。
附上sql:
select azjid,asl,bzjid,bsl from(
select a.zjid azjid,a.sl asl,b.zjid bzjid,b.sl bsl from
(
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
) a left join(
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
) b on (SUBSTR(a.zjid,1,2)) != (SUBSTR(b.zjid,1,2))
) where (SUBSTR(azjid,3,1)) = (SUBSTR(bzjid,3,1))
结果：
6、最后两个sql，做个左连接查询。然后结果怎么都不对。。感觉数据上有问题。有点懵。。。。。
需要将已经配完后的数据不在继续匹配，详细见图片。
下面的是我的代码。大神，你可以看下，看能否帮忙修改下，以达到我的需求，谢谢啦
with T1 as (
select 'KCP001' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP001' cpid,'B' zjlx,'KB10' zjid from dual union all
select 'KCP002' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP002' cpid,'B' zjlx,'KB20' zjid from dual union all
select 'KCP003' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP003' cpid,'B' zjlx,'KB40' zjid from dual union all
select 'KCP004' cpid,'A' zjlx,'KA40' zjid from dual union all
select 'KCP004' cpid,'B' zjlx,'KB30' zjid from dual union all
select 'KCP005' cpid,'A' zjlx,'KA30' zjid from dual union all
select 'KCP005' cpid,'B' zjlx,'KB30' zjid from dual
), T2 as(
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
)
select cpid,zjlx,zjid,ptcpsl+decode(ptcpsl,0,0,ss) zjsl,ptcpsl,decode(ptcpsl,0,0,ss) bptcpsl
from
(select m.cpid,m.zjlx,m.zjid,m.ptcpsl,
nvl(n.sl-m.sm,0) ss,
nvl(min(n.sl-m.sm) over(partition by m.zjid order by rownum),0) ab
from
(select t.*,sum(ptcpsl) over(partition by zjid order by rownum) sm
from
(select a.*,
case when nvl(b.sl,0)>nvl(d.sl,0) then nvl(d.sl,0)
when nvl(b.sl,0) else b.sl end ptcpsl
from t1 a,t2 b,t1 c,t2 d
where a.zjid = b.zjid(+)
and a.cpid=c.cpid
and a.zjidc.zjid
and c.zjid = d.zjid(+)
order by 1,2 ) t
) m,t2 n
where m.zjid=n.zjid(+)
order by 1,2)
;
有点不明白这里的业务逻辑了。。好像A和B两种业务逻辑，一会是A，一会是B
楼主说的配完后的数据不在继续匹配，我不明白是优先匹配A类型还是B类型。业务逻辑来说，我可能匹配出A也可以匹配出B，我应该将可能性都显示出来。
附上图片吧：
或许可以合并一起生产，也可以单独生产吧
我想的是，KA10和KB10组成KCP001。无论KCP001类型是A还是B。我首先会罗列出可以的选项。
如50个KA10和50个KB10。。可以生产KCP001 A 50个 和KCP001 B 50个，意思就是相互之间匹配成功同时能够生产出成品的数量。
select CPID,ZJLX,zjid,zjsl,PTCPSL,(case when ptcpsl=0 then zjsl else zjsl-ptcpsl end) BPTCPSL from
(
SELECT CPID,ZJLX,zjid,zjsl,nvl(bsl,0) PTCPSL from
(
select cpid,zjlx,zjid,(nvl(sl,0)) zjsl
from(
select t1.cpid,t1.zjlx,t1.zjid,t2.sl from (
select 'KCP001' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP001' cpid,'B' zjlx,'KB10' zjid from dual union all
select 'KCP002' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP002' cpid,'B' zjlx,'KB20' zjid from dual union all
select 'KCP003' cpid,'A' zjlx,'KA10' zjid from dual union all
select 'KCP003' cpid,'B' zjlx,'KB40' zjid from dual union all
select 'KCP004' cpid,'A' zjlx,'KA40' zjid from dual union all
select 'KCP004' cpid,'B' zjlx,'KB30' zjid from dual union all
select 'KCP005' cpid,'A' zjlx,'KA30' zjid from dual union all
select 'KCP005' cpid,'B' zjlx,'KB30' zjid from dual
)t1 left join (
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
)t2 on t1.zjid=t2.zjid order by cpid asc,zjlx asc
)
)M left join
(
select azjid,asl,bzjid,bsl from(
select a.zjid azjid,a.sl asl,b.zjid bzjid,b.sl bsl from
(
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
) a left join(
select 'KA10' zjid,50 sl,20160819 rq from dual union all
select 'KB10' zjid,50 sl,20160819 rq from dual union all
select 'KB20' zjid,20 sl,20160819 rq from dual union all
select 'KB30' zjid,60 sl,20160819 rq from dual union all
select 'KA40' zjid,10 sl,20160819 rq from dual
) b on (SUBSTR(a.zjid,1,2)) != (SUBSTR(b.zjid,1,2))
) where (SUBSTR(azjid,3,1)) = (SUBSTR(bzjid,3,1))
)N
on m.zjid = azjid
)
结果：
我对楼主说的这块逻辑也不是懂。我就是随便说一说。不对的地方，还希望大家包涵。