超难的问题：sql怎样实现数量分配，并取后一个分配的日期

[wongwhbbo]

请各位大侠帮助：
表1：
物料代码 数量
A001 600
A001 800
A001 500
A002 300
A002 400
表2：
物料代码 数量 采购交期
A001 200 2019/1/10
A001 400 2019/1/21
A001 300 2019/1/25
A002 100 2019/1/11
A002 400 2019/1/14

希望实现的效果：
物料代码 数量 最后满足交期
A001 600 2019/1/21
A001 800 2019/1/25
A001 500 2019/1/25
A002 300 2019/1/14
A002 400 2019/1/14
临时表：
Create table #Tab(
[物料] varchar(50),
[数量] int,
)
Create table #Tab2(
[物料] varchar(50),
[数量] int,
[入库日期] datetime
)
INSERT INTO #tab
select 'A001',300 union all
select 'A001',400 union all
select 'A001',800 union all
select 'A002',200 union all
select 'A002',600

INSERT INTO #tab2
select 'A001',200,'2019/01/01' union all
select 'A001',400,'2019/01/02'union all
select 'A001',800,'2019/01/06' union all
select 'A002',100,'2019/01/08' union all
select 'A002',100,'2019/01/09'

[砸死牛顿的苹果]

2个表 各自累加 之后的 连接,对比 就好了吧

[wongwhbbo]

我试了用累加，实现不了，我用了游标来实现，但感觉不是最优方法，所以请大侠们帮助，谢

[wongwhbbo]

兄好，能否麻烦给一个语句学习下，谢

[Dear SQL(燊)]

Create table #Tab(
[物料] varchar(50),
[数量] int,
)
Create table #Tab2(
[物料] varchar(50),
[数量] int,
[入库日期] datetime
)
INSERT INTO #tab
select 'A001',200 union all
select 'A001',1000 union all
select 'A001',1800 union all
select 'A002',200 union all
select 'A002',600

INSERT INTO #tab2
select 'A001',200,'2019/01/01' union all
select 'A001',400,'2019/01/02'union all
select 'A001',800,'2019/01/06' union all
select 'A002',100,'2019/01/08' union all
select 'A002',100,'2019/01/09'

;
with list1 as(
	select *,endqty=case when lead([数量])over (partition by [物料] order by [入库日期]) is null then 999999999999999999999
						 else sum([数量])over(partition by [物料] order by [入库日期])
					end
		,startqty=sum([数量])over(partition by [物料] order by [入库日期])-[数量]
	from #tab2
),list2 as(
	select *,sumqty=sum([数量])over(partition by [物料] order by [数量]  ) - [数量]
	from #tab
)
select a.[物料],a.[数量],b.入库日期 --,B.*
from list2 a
left join list1 b on a.物料=b.物料 and a.[数量] between b.startqty and b.endqty

物料                                                 数量          入库日期
-------------------------------------------------- ----------- -----------------------
A001                                               200         2019-01-01 00:00:00.000
A001                                               1000        2019-01-01 00:00:00.000
A001                                               1800        2019-01-01 00:00:00.000
A002                                               200         2019-01-09 00:00:00.000
A002                                               600         2019-01-09 00:00:00.000

[Dear SQL(燊)]

if object_id('tempdb..#tab') is not null drop table #tab
if object_id('tempdb..#Tab2') is not null drop table #Tab2

Create table #Tab(
[物料] varchar(50),
[数量] int,
)
Create table #Tab2(
[物料] varchar(50),
[数量] int,
[入库日期] datetime
)
INSERT INTO #tab
select 'A001',200 union all
select 'A001',1000 union all
select 'A001',1800 union all
select 'A002',200 union all
select 'A002',600

INSERT INTO #tab2
select 'A001',200,'2019/01/01' union all
select 'A001',400,'2019/01/02'union all
select 'A001',800,'2019/01/06' union all
select 'A002',100,'2019/01/08' union all
select 'A002',100,'2019/01/09'

;
with list1 as(
	select *,endqty=case when lead([数量])over (partition by [物料] order by [入库日期]) is null then 999999999999999999999
						 else sum([数量])over(partition by [物料] order by [入库日期])
					end
		,startqty=sum([数量])over(partition by [物料] order by [入库日期])-[数量]
	from #tab2
),list2 as(
	select *,sumqty=sum([数量])over(partition by [物料] order by [数量]  ) - [数量]
	from #tab
)
select a.[物料],a.[数量],b.入库日期 --,B.*
from list2 a
left join list1 b on a.物料=b.物料 and a.[数量] > b.startqty and a.[数量]<=b.endqty

物料                                                 数量          入库日期
-------------------------------------------------- ----------- -----------------------
A001                                               200         2019-01-01 00:00:00.000
A001                                               1000        2019-01-06 00:00:00.000
A001                                               1800        2019-01-06 00:00:00.000
A002                                               200         2019-01-09 00:00:00.000
A002                                               600         2019-01-09 00:00:00.000

[wongwhbbo]

非常感谢兄的帮助

[wongwhbbo]

我用的是sql2008,兄用的lead函数用不了，但还是感 谢兄

[Dear SQL(燊)]

引用 8 楼 wongwhbbo 的回复:

我用的是sql2008,兄用的lead函数用不了，但还是感 谢兄

先用row_number()生成行号，再用left join on rid=rid-1