入库数分配到订单

[marongc]

订单号为17001
订单 项 物料 订单数
-----------------------------------------
17001 1 A01 100
17001 2 A01 100
17001 3 A01 100
然后送货来了4次, 分别是80,80,80,55
入库单
18001 1 A01 80
18001 2 A01 80
18001 3 A01 80
18001 4 A01 55
想要的结果:
订单 物料 订单数 送货数
----------------------------------------
1 A01 100 100
3 A01 100 100
4 A01 100 95

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([订单] int,[项] int,[物料] nvarchar(23),[订单数] int)
Insert #T
select 17001,1,N'A01',100 union all
select 17001,2,N'A01',100 union all
select 17001,3,N'A01',100
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([入库单] int,[项] int,[物料] nvarchar(23),送货数 int)
Insert #T2
select 18001,1,N'A01',80 union all
select 18001,2,N'A01',80 union all
select 18001,3,N'A01',80 union all
select 18001,4,N'A01',55
Go
--测试数据结束
;WITH temp AS (
SELECT *,(SELECT SUM(送货数) FROM #T2) AS 送货数 FROM #T WHERE 项=1
UNION ALL
SELECT  a.订单 ,
        a.项 ,
        a.物料 ,
        ( CASE WHEN b.送货数 - a.订单数 > a.订单数 THEN a.订单数
               ELSE b.送货数 - a.订单数
          END ) AS 订单数 ,
        b.送货数- a.订单数
FROM    #T a
        JOIN temp b ON a.项 - 1 = b.项
)
SELECT 订单,项,物料,订单数 FROM temp

[RINK_1]

SELECT A.*, CASE WHEN TOTAL-SUB_TOTAL>=[订单数] THEN [订单数] WHEN TOTAL-SUB_TOTAL<=0 THEN 0 WHEN TOTAL-SUB_TOTAL<[订单数] THEN TOTAL-SUB_TOTAL END AS 送货数 FROM TABLE1 A OUTER APPLY (SELECT ISNULL(SUM([订单数]),0) AS SUB_TOTAL FROM TABLE1 WHERE [订单]=A.[订单] AND [项]<A.[项]) AS B JOIN (SELECT SUM(送货数) AS TOTAL,[物料] FROM TABLE2 GROUP BY [物料]) AS C ON A.[物料]=C.[物料]

[marongc]

大家都在休息啊