请大家看连接http://topic.csdn.net/u/20100803/15/1dd9841d-d75c-40c2-9c8d-fe5a9a6b7458.html?seed=657398232&r=67443935#r_67443935
环境为:oracle10g,PL/sql,windowsxp,我的原来需求是这样的我有个采购明细表purorder_ctl 其中有字段po_id明细的id,respond_date 供应商回复发货日,purorder_qty 为采购数量,rct_po_qty 为已经收料数量 还有一张表为供应商回复的交期表,是purorder_ctl 的子表,po_respond_d其中有purorder_ctl中的采购明细的id字段po_id,respond_date供应商交货日期。交货数量 bill_qty,
其中每一条purorder_ctl 中的记录可能对应多个供应商回复明细,也就是说一个采购明细供应商可能分批发货,purorder_ctl和po_respond_d关系为一对多。
供应商回复发货日以po_respond_d日期为准,如果po_respond_d中的bill_qty和小于purorder_ctl中的purorder_qty,那么这个purorder_qty - sum(bill_qty)的发货日期默认为
purorder_ctl中的发货日期respond_date,
假如 purorder_ctl( po_id,respond_date,purorder_qty,rct_po_qty)(PO0001,'2010/08/04',100,60)
po_respond_d(po_id,respond_date,bill_qty)(PO0001,'2010/08/05',10) (PO0001,'2010/08/06',70) (PO0001,'2010/08/05',10)
现在我想得到的是每个日期的未收料量,已经收料量按日期的先后分给每个收料日期,剩余的就是该日期未收料量:
以上;例子想得到的结果就是 :PO_id, date,qty分别为采购明细id,date预计收货日,qty未收货数量,
po_id , date ,qty
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/05',10)
请问大家能否用一条sql搞定。
环境为:oracle10g,PL/sql,windowsxp,我的原来需求是这样的我有个采购明细表purorder_ctl 其中有字段po_id明细的id,respond_date 供应商回复发货日,purorder_qty 为采购数量,rct_po_qty 为已经收料数量 还有一张表为供应商回复的交期表,是purorder_ctl 的子表,po_respond_d其中有purorder_ctl中的采购明细的id字段po_id,respond_date供应商交货日期。交货数量 bill_qty,
其中每一条purorder_ctl 中的记录可能对应多个供应商回复明细,也就是说一个采购明细供应商可能分批发货,purorder_ctl和po_respond_d关系为一对多。
供应商回复发货日以po_respond_d日期为准,如果po_respond_d中的bill_qty和小于purorder_ctl中的purorder_qty,那么这个purorder_qty - sum(bill_qty)的发货日期默认为
purorder_ctl中的发货日期respond_date,
假如 purorder_ctl( po_id,respond_date,purorder_qty,rct_po_qty)(PO0001,'2010/08/04',100,60)
po_respond_d(po_id,respond_date,bill_qty)(PO0001,'2010/08/05',10) (PO0001,'2010/08/06',70) (PO0001,'2010/08/05',10)
现在我想得到的是每个日期的未收料量,已经收料量按日期的先后分给每个收料日期,剩余的就是该日期未收料量:
以上;例子想得到的结果就是 :PO_id, date,qty分别为采购明细id,date预计收货日,qty未收货数量,
po_id , date ,qty
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/05',10)
请问大家能否用一条sql搞定。
po_respond_d(po_id,respond_date,bill_qty)(PO0001,'2010/08/05',10) (PO0001,'2010/08/06',70) (PO0001,'2010/08/05',10)
结果:po_id , date ,qty
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/05',10)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/05',10)
问题:(PO0001,2010/08/04,0) PO和日期没有引号,
2:为什么同样是'2010/08/05' 一个为0 ,一个为10
为什么6号分配30?100-70?
po_respond_d(po_id,respond_date,bill_qty)(PO0001,'2010/08/05',10) (PO0001,'2010/08/06',70) (PO0001,'2010/08/05',10)
有2个2010/08/05日期的值?
最后得到结果的 qty 的值 是怎么得来?100-10 是5号?
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/07',10)
最后一个是我写错了,分批回复总量为90,还有10个就默认为采购明细中的回复日期2010/08/04,已收货是60,也就是2010/08/04的10已收到,未收到就为0,'2010/08/05‘也为0,'2010/08/06'预计收货是70,前面已分配了20,还有40个已收货数量,70-40=30所以'2010/08/06'日的未收货为30,'2010/08/07' 当然为0了
这个数据得到的 qty 就是 某一天未收货的数量select purorder_ctl.po_id,
po_respond_d.respond_date date,
purorder_ctl.purorder_qty - sum(bill_qty)over(order by po_respond_d.respond_date)qty
from (select 'PO0001' po_id, '2010/08/04' respond_date,100 purorder_qty, 60 rct_po_qty from dual)
purorder_ctl,
(select 'PO0001' po_id, '2010/08/05' respond_date, 10 bill_qty from dual
union all
select 'PO0001' po_id, '2010/08/06' respond_date, 70 bill_qty from dual
union all
select 'PO0001' po_id, '2010/08/07' respond_date, 10 bill_qty from dual)
po_respond_d
where purorder_ctl.po_id = po_respond_d.po_id
就得到这样的结果:
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/07',10)
就得到这样的结果:
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/07',10)
SELECT tp.po_id,t3.respond_date,tp.rct_po_qty , t3.fhsl,t3.fhzl
,CASE WHEN tp.rct_po_qty - t3.fhzl >0 THEN 0 ELSE (CASE WHEN t3.fhzl-tp.rct_po_qty > t3.fhsl THEN t3.fhsl ELSE t3.fhzl-tp.rct_po_qty END) END wfhsl
FROM purorder_ctl tp,(
SELECT t2.po_id,t2.respond_date,t2.fhsl,SUM(t2.fhsl) OVER(PARTITION BY t2.po_id ORDER BY t2.respond_date) fhzl
FROM (
SELECT t1.po_id,t1.respond_date,SUM(t1.fhsl) fhsl
FROM (
SELECT p.po_id,p.respond_date,p.purorder_qty-t0.fhzl fhsl
FROM purorder_ctl p,(SELECT po.po_id,SUM(po.bill_qty) fhzl FROM po_respond_d po GROUP BY po.po_id) t0
WHERE p.po_id=t0.po_id
union
SELECT po.po_id,po.respond_date,po.bill_qty fhsl FROM po_respond_d po ) t1
WHERE t1.fhsl>0 GROUP BY t1.po_id,t1.respond_date ) t2 ) t3
WHERE tp.po_id=t3.po_id
ORDER BY 1,2
1、供应商的某个货号的所有发货信息已经在表po_respond_d中了,所以该表中的发货数合计与表purorder_ctl中的采购数差额就属于表purorder_ctl中日期的发货数(也就是如果表po_respond_d没有数据,则表purorder_ctl中的发货数=采购数)
2、按日期顺序计算,未收货数=
(总收货数 > 累计到当日已发货数)--> 0
ELSE
(累计到当日已发货数 - 总收货数 > 当日发货数) --> 当日发货数
ELSE 累计到当日已发货数 - 总收货数
from (
select po_id,respond_date,rct_po_qty,bill_qty,remain,lag(remain)over(order by respond_date) as finished
from(
select pc.po_id,p.respond_date,pc.rct_po_qty,p.bill_qty,pc.rct_po_qty-p.bill_qty as remain
from purorder_ctl pc,
(select p.po_id,p.respond_date,sum(p.bill_qty) as bill_qty from po_respond_d p
group by p.po_id,p.respond_date) p
where pc.po_id(+)=p.po_id
)
)
where remain <0
在次写写楼上,写的很准确应该是个高手。