求sql多行数量与合并后的数量计算合格率
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)

表testa明细档，testb合计的合格总数，求明细档的合格率
例如testa表的第一个G994E0052A，数量为testb 2100大于2000即为合格率为100%，结余的数量为100，不够第二行的2000量合格率为0.05,
如下图所示

sql语句如何写
[解决办法]

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 
on s.px=n.px+1 and s.partnum=n.partnum
)
--select * from n order by partnum,px 
 
select
lotnum,
partnum,
qnty,
left(ltrim(case when lf>=0 then 100 else ((qnty-abs(lf))*100.0)/qnty end),6)+'%' as 合格率
from n

/*
lotnumpartnumqnty合格率
----------------------------
MP1211004623C024E0650A10000100.00%
MP1211003661G994E0052A2000100.00%
MP1211001845S024E0312B2952093.224%
MP1211001484S024E0327A8500100.00%
MP1211003662G994E0052A20005.0000%
MP1211004624C024E0650A10000100.00%
MP1211004625C024E0650A100000.0000%
*/


--修改了一下