日期:2014-05-17 浏览次数:20687 次
CREATE TABLE testa
(
lotnum VARCHAR(20) ,
partnum VARCHAR(20),
qnty int
)
INSERT INTO TESTA VALUES('MP1211003661','G994E0052A',2000)
INSERT INTO TESTA VALUES('MP1211003662','G994E0052A',2000)
INSERT INTO TESTA VALUES('MP1211001484','S024E0327A',8500)
INSERT INTO TESTA VALUES('MP1211001845','S024E0312B',29520)
INSERT INTO TESTA VALUES('MP1211004623','C024E0650A',10000)
INSERT INTO TESTA VALUES('MP1211004624','C024E0650A',10000)
INSERT INTO TESTA VALUES('MP1211004625','C024E0650A',10000)
CREATE TABLE testb
(
partnum VARCHAR(20),
qnty int
)
INSERT INTO TESTB VALUES('G994E0052A',2100)
INSERT INTO TESTB VALUES('S024E0327A',18500)
INSERT INTO TESTB VALUES('S024E0312B',27520)
INSERT INTO TESTB VALUES('C024E0650A',20000)
;with t
as(
select
px=row_number()over(partition by partnum order by getdate()),
*
from
testa
),
m as
(
select
px=row_number()over(partition by partnum order by getdate()),
*
from
testb
),
s as(
select
t.px,
t.lotnum,
t.partnum,
t.qnty,
m.qnty as jy
from
t
left join
m
on t.px=m.px and t.partnum=m.partnum
)
--select * from s
,
n
as(
select px,lotnum,partnum,qnty,jy,jy-qnty as lf from s where px=1
union all
select s.px,s.lotnum,s.partnum,s.qnty,s.jy,n.lf-s.qnty from s inner join n