表结构和测试数据如下
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
请大家帮忙，谢谢。
