存储过程改成一条sql

Question

请大家看连接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搞定。

Answer 1

可以看看上面了说的很清楚啊，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
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/05',10)

Answer 2

(PO0001,2010/08/04,0)
(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? 

Answer 3

楼主举例的数据对么？
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号？

Answer 4

(PO0001,2010/08/04,0)
(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了

Answer 5

还是不太清楚你的想法，试着给你写个，做个参考
这个数据得到的 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

Answer 6

date 是个关键字 取个另外的别名吧

Answer 7

重新讲一遍（数据示例）：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/07',10)需要预计收货日期和数量就是（'2010/08/04'，10）（'2010/08/05',10）（'2010/08/06',70）（'2010/08/07',10）其中'2010/08/04'的10个预计收货就是100-（10+10+70）得出的，总共是已收货为60，按日期从小到大来分这60个，'2010/08/04'预计收货是10，从60中给他10，所以'2010/08/04'未收货为0，已收货剩下50，同理'2010/08/05'为收货为0，已收货为40了，'2010/08/06'，预计收货是70，70-40=30，已收货为0了，'2010/08/07'的未收货自然为0
就得到这样的结果：
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/07',10)

Answer 8

重新讲一遍（数据示例）：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/07',10)需要预计收货日期和数量就是（'2010/08/04'，10）（'2010/08/05',10）（'2010/08/06',70）（'2010/08/07',10）其中'2010/08/04'的10个预计收货就是100-（10+10+70）得出的，总共是已收货为60，按日期从小到大来分这60个，'2010/08/04'预计收货是10，从60中给他10，所以'2010/08/04'未收货为0，已收货剩下50，同理'2010/08/05'为收货为0，已收货为40了，'2010/08/06'，预计收货是70，70-40=30，已收货为0了，'2010/08/07'的未收货自然为10
就得到这样的结果：
(PO0001,2010/08/04,0)
(PO0001,'2010/08/05',0)
(PO0001,'2010/08/06',30)
(PO0001,'2010/08/07',10)

Answer 9

--货号 发货日期 总收货数 当日发货数 累计发货数 未收货数
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

Answer 10

呵呵，可以考虑，至少又学了个oracle的OVer函数，

Answer 11

楼主的意思：（个人理解）
1、供应商的某个货号的所有发货信息已经在表po_respond_d中了，所以该表中的发货数合计与表purorder_ctl中的采购数差额就属于表purorder_ctl中日期的发货数（也就是如果表po_respond_d没有数据，则表purorder_ctl中的发货数=采购数）
2、按日期顺序计算，未收货数=
  （总收货数 > 累计到当日已发货数）--> 0
  ELSE
   （累计到当日已发货数 - 总收货数 > 当日发货数） --> 当日发货数
    ELSE 累计到当日已发货数 - 总收货数

Answer 12

select po_id,respond_date,bill_qty-finished as need
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 

Answer 13

应该是在途货物数量这个结构设计挺怪的，为什么不直接在purorder_ctl表中增加一个表示这个表日期的发货数量或未发货数量，或者直接在明细表中把2010/08/04那条记录增加进去？

Answer 14

其实是这样的purorder_ctl这个表是架构系统的时候就设计好的，其中的respond_date栏位是按照销售订单里分解出来的料号，按照客户的需求日，和前制期系统算出来的，一下采购单系统就自动带出来了，后来发现供应商不是每次都能到货，所以就要了供应商交期回复这张表po_respond_d，但是采购部门填写也不是很准确，分批的数量总是小于明细中的总数，这部分就没有交期了，所以我就默认为系统计算出来的那个日期。purorder_ctl 【respond_date】
在次写写楼上，写的很准确应该是个高手。