34,837
社区成员




create table tb_qty
(
KPNO varchar(10),
QTY float
)
insert into tb_qty(KPNO,QTY) values('A01','100')
insert into tb_qty(KPNO,QTY) values('A02','100')
insert into tb_qty(KPNO,QTY) values('A03','100')
insert into tb_qty(KPNO,QTY) values('A04','100')
insert into tb_qty(KPNO,QTY) values('A05','100')
create table tb_ODNO
( SDATE varchar(8),
WKNO varchar(10),
PNO varchar(10),
KPNO varchar(10),
NQTY float
)
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111111','100001','AAA','A01','80')
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111111','100001','AAA','A02','60')
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111111','100001','AAA','A03','50')
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111112','100002','BBB','A01','60')
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111112','100002','BBB','A02','100')
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111112','100002','BBB','A03','50')
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111112','100002','BBB','A04','120')
insert into tb_ODNO(SDATE,WKNO,PNO,KPNO,NQTY)values('20111112','100002','BBB','A05','120')
根据SDATE的顺序分配QTY,已分配的数量在QTY里要减掉
要求结果如下
SDATE WKNO PNO KPNO NQTY OQTY
------------------------------------------------
20111111 100001 AAA A01 80 0
20111111 100001 AAA A02 60 0
20111111 100001 AAA A03 50 0
20111112 100002 BBB A01 60 -40
20111112 100002 BBB A02 100 -60
20111112 100002 BBB A03 50 0
20111112 100002 BBB A04 120 -20
20111112 100002 BBB A05 120 -20
请大家帮忙,谢谢。
select a.*,OQTY=case when Qty-(select sum(NQTY) from tb_odno where SDATE<=a.SDATE and KPNO=a.KPNO)>0 then 0 else Qty-(select sum(NQTY) from tb_odno where SDATE<=a.SDATE and KPNO=a.KPNO) end from tb_odno a inner join tb_qty b on a.KPNO=b.KPNO
/*
SDATE WKNO PNO KPNO NQTY OQTY
-------- ---------- ---------- ---------- ---------------------- ----------------------
20111111 100001 AAA A01 80 0
20111111 100001 AAA A02 60 0
20111111 100001 AAA A03 50 0
20111112 100002 BBB A01 60 -40
20111112 100002 BBB A02 100 -60
20111112 100002 BBB A03 50 0
20111112 100002 BBB A04 120 -20
20111112 100002 BBB A05 120 -20
(8 行受影响)
*/
select b.*
,case when a.qty >= (select sum(nqty) from tb_ODNO where KPNO = b.KPNO and (SDATE < b.SDATE
or SDATE = b.SDATE and WKNO< b.WKNO
or SDATE = b.SDATE and WKNO= b.WKNO and PNO <= b.PNO)
) then 0 else a.qty - (select sum(nqty) from tb_ODNO where KPNO = b.KPNO and (SDATE < b.SDATE
or SDATE = b.SDATE and WKNO< b.WKNO
or SDATE = b.SDATE and WKNO= b.WKNO and PNO <= b.PNO)
) end
from tb_qty a,tb_ODNO b
where a.KPNO = b.KPNO