求问：两个表相递归减

Question

怎样才能算出来第一张表中的【订单数量】在第二张表哪些数量才能满足，例如：表1中的第一行，需求数量10，那么在第二个表中需要第一行，二行才能满足，那么把和表2中的采购订单号、要求交货日期更新到表1中去。

Answer 1

with t1(序号,订单号,要求日期,物料编码,订单数量) as (
	select 1,'so21001',CONVERT(date,'2021-6-10'),1001,10
	union all select 2,'so21002','2021-6-11',1001,20
	union all select 3,'so21003','2021-6-12',1001,10
	union all select 4,'so21004','2021-6-13',1001,50
	union all select 5,'so21005','2021-6-14',1001,60
	union all select 6,'so21006','2021-6-15',1001,100
	union all select 7,'so21007','2021-6-16',1001,30
	union all select 8,'so21008','2021-6-17',1001,10
	union all select 9,'so21009','2021-6-18',1001,20
	union all select 10,'so21010','2021-6-19',1001,10
),t2(序号,采购订单号,要求交货日期,物料编码,订单数量) as (
	select 1,'po21001',CONVERT(date,'2021-6-2'),1001,5
	union all select 2,'po2','2021-6-3',1001,10
	union all select 3,'po3','2021-6-4',1001,30
	union all select 4,'po4','2021-6-5',1001,100
	union all select 5,'po5','2021-6-5',1001,60
	union all select 6,'po6','2021-6-7',1001,20
),t3 as (
	select *,ROW_NUMBER() over(partition by 物料编码 order by 要求日期,序号) as rid 
	from t1
),表1 as (
	select * from t3 a
	cross apply (
		select SUM(订单数量) as 累计需求数量
		from t3
		where 物料编码=a.物料编码 and rid<=a.rid
	) b
),t5 as (
	select *,ROW_NUMBER() over(partition by 物料编码 order by 要求交货日期,序号) as rid 
	from t2
),表2 as (
	select * from t5 a
	cross apply (
		select SUM(订单数量) as 累计在途数量
		from t5
		where 物料编码=a.物料编码 and rid<=a.rid
	) b
)
-- 可以忽视前边的内容
select a.*, 采购订单号,要求交货日期
from 表1 a
outer apply (
	select top 1 * 
	from 表2
	where 物料编码=a.物料编码
	and 累计在途数量>=a.累计需求数量
	order by 累计在途数量
) b