求一SQL，看看能不能实现

[bibidbzfish]

表A
Storageid     productid    billno            billdate               rknum
001                00001         DJ001      2017-01-01           10000
001                00001         DJ002      2017-01-02            20000
001                00001         DJ003      2017-01-03            30000
002                00002         DJ004      2017-01-01           10000
002                00002         DJ005      2017-01-02           20000
002                00002         DJ006      2017-01-03           30000
002                00002         DJ007      2017-01-04           10000
表B
Storageid     productid             cknum
001                00001                 36000
002                00002                  45800
得到表 C
Storageid     productid    billno            billdate               rknum               cknum                 yenum(rknum - cknum)
001                00001         DJ001      2017-01-01           10000               10000                         0
001                00001         DJ002      2017-01-02            20000               20000                        0
001                00001         DJ003      2017-01-03            30000              6000                          24000
002                00002         DJ004      2017-01-01           10000              10000                          0
002                00002         DJ005      2017-01-02           20000               20000                         0
002                00002         DJ006      2017-01-03           30000               15800                        14200
002                00002         DJ007      2017-01-04           10000                0                                 10000

先进先出，根据产品的出库数据去填充入库数据，类似得到产品的一个批次（billno）库存。跪求大神有更好的思路解决这个问题，个人感觉上面的思路处理的数据量有点大，需要提取所有的单据。另外数据库结构已经定死，不能更改。

[卖水果的net]

表 B中，一个产品有多条记录时，怎么显示呢？

[RINK_1]

SELECT A.*, CASE WHEN C.cknum>B.SUBTOTAL THEN A.rknum WHEN C.cknum<B.SUBTOTAL-A.rknum THEN 0 WHEN C.cknum <B.SUBTOTAL AND C.cknum>B.SUBTOTAL-A.rknum THEN C.cknum-B.SUBTOTAL+A.rknum END AS CKUNM FROM 表A A OUTER APPLY (SELECT SUM(RKNUM) AS SUBTOTAL FROM 表A WHERE productid=A.productid AND Storageid=A.Storageid AND billdate<=A.billdate) AS B JOIN 表B C ON A.Storageid=C.Storageid AND A.productid=C.productid

[骑着蜗牛去爬山]

with tbA(Storageid,productid,billno,billdate,rknum)
as(
select '001','00001','DJ001','2017-01-01',10000 union all
select '001','00001','DJ002','2017-01-02',20000 union all
select '001','00001','DJ003','2017-01-03',30000 union all
select '002','00002','DJ004','2017-01-01',10000 union all
select '002','00002','DJ005','2017-01-02',20000 union all
select '002','00002','DJ006','2017-01-03',30000 union all
select '002','00002','DJ007','2017-01-04',10000),
tbB(Storageid,productid,cknum)
as(
select '001','00001',36000 union all
select '002','00002',45800
),
tbC as(
select tbA.*,cknum,rownum=ROW_NUMBER()over(partition by tbA.storageid,tbA.productid order by billdate) from tbA join tbB on tbA.Storageid=tbB.Storageid and tbA.productid=tbB.productid
),tbD as(select Storageid,productid,billno,billdate,rknum,(case when isnull((select sum(rknum) from tbC tbC2 where tbC1.Storageid=tbC2.Storageid and tbC1.productid=tbC2.productid and tbC2.rownum<tbC1.rownum),0)+tbC1.rknum<=tbC1.cknum then tbC1.rknum else (case when cknum- isnull((select sum(rknum) from tbC tbC2 where tbC1.Storageid=tbC2.Storageid and tbC1.productid=tbC2.productid and tbC2.rownum<tbC1.rownum),0)>=0 then cknum- isnull((select sum(rknum) from tbC tbC2 where tbC1.Storageid=tbC2.Storageid and tbC1.productid=tbC2.productid and tbC2.rownum<tbC1.rownum),0) else 0 end) end) as cknum from tbC tbC1)
select *,rknum - cknum as yenum from tbD