要求3个结果

[weisai]

物料表
物料 是否启动批号 是否可整包发料（库存足够时） 包装数量（是否可整包发料为1才有）
A 0 0 0
B 1 0 0
C 1 1 20
D 0 1 100
E 1 1 50

是否可整包发料（库存足够时） 的意思是， 比如C的包装数量是20 ，如果需要发料 30 ，库存只有35，那么就直接发30（不整包发）。
如果库存有100，就按整包发40（2*20），多发了10（40-30）



库存表
物料 批号 库存
A 20
B B001 20
B B002 10
B B003 50
C C001 20
C C002 25
C C003 200
D 220
E E001 5
E E002 10


需要发料表
物料 需要发料数量（总数）
A 60
B 50
C 75
D 110
E 60


要求下面结果
1、发料结果（批号按批号的顺序，先进先出）
物料 批号 发料数量
A 20
B B001 20
B B002 10
B B003 20
C C001 20
C C002 25
C C003 35
D 110
E E001 5
E E002 10

2 多发结果
物料 批号 发料数量
C C003 5
D 90

解析一下： 多发，只有物料 是否可整包发料为1 才有， 第一个结果批号C003 ，发了 35， C的包装数量是20 ，并且批号C003库存足够20 的2倍，所以按40发，多发了5.
同样道理，D 库存足够发 100 的2倍，多发是200-110 =90

3 汇总结果
物料 需发 发料（总） 实发数量 多发
A 60 20 20 0
B 50 50 50 0
C 75 75 80 5
D 110 110 200 90
E 60 15 15 0

[二月十六]

C003 为啥是35？c要75，c001 20 ,c002 25,还差30，c003应该发40，然后多10，为啥是发35多5？

[二月十六]

测试数据：

--测试数据
if not object_id(N'Tempdb..#物料表') is null
	drop table #物料表
Go
Create table #物料表([物料] nvarchar(21),[是否启动批号] int,[是否可整包发料] int,[包装数量] int)
Insert #物料表
select N'A',0,0,0 union all
select N'B',1,0,0 union all
select N'C',1,1,20 union all
select N'D',0,1,100 union all
select N'E',1,1,50
GO
if not object_id(N'Tempdb..#库存表') is null
	drop table #库存表
Go
Create table #库存表([物料] nvarchar(21),[批号] nvarchar(24),[库存] int)
Insert #库存表
select N'A',null,20 union all
select N'B',N'B001',20 union all
select N'B',N'B002',10 union all
select N'B',N'B003',50 union all
select N'C',N'C001',20 union all
select N'C',N'C002',25 union all
select N'C',N'C003',200 union all
select N'D',null,220 union all
select N'E',N'E001',5 union all
select N'E',N'E002',10
GO
if not object_id(N'Tempdb..#需要发料表') is null
	drop table #需要发料表
Go
Create table #需要发料表([物料] nvarchar(21),[需要发料数量] int)
Insert #需要发料表
select N'A',60 union all
select N'B',50 union all
select N'C',75 union all
select N'D',110 union all
select N'E',60
Go
--测试数据结束

1、

;WITH ctea AS (
SELECT
    #库存表.物料,
    ISNULL(批号, 'A001') AS 批号,
    库存,
    是否可整包发料,
    包装数量,
	ROW_NUMBER()OVER(PARTITION BY #库存表.物料 ORDER BY ISNULL(批号, 'A001')) rn
FROM
    #库存表
    JOIN
        #物料表
            ON #物料表.物料 = #库存表.物料
),cteb AS (
SELECT
    ctea.*,
    #需要发料表.需要发料数量 - ctea.库存 AS tempkc,
	CASE WHEN #需要发料表.需要发料数量 - ctea.库存>0 THEN ctea.库存 ELSE #需要发料表.需要发料数量 END  发料数量
FROM
    #需要发料表
    JOIN
        ctea
            ON ctea.物料 = #需要发料表.物料
               AND ctea.rn = 1
UNION ALL
SELECT ctea.*,cteb.tempkc-ctea.库存 AS tempkc, 
CASE WHEN tempkc - ctea.库存>0 THEN ctea.库存 ELSE tempkc END  发料数量
FROM cteb JOIN ctea ON ctea.物料 = cteb.物料 AND ctea.rn = cteb.rn+1 AND cteb.tempkc>0
)
SELECT 物料,CASE WHEN 批号='A001' THEN '' ELSE 批号 END 批号,cteb.发料数量 FROM cteb ORDER BY cteb.物料,cteb.批号

[二月十六]

2、

;WITH ctea AS (
SELECT
    #库存表.物料,
    ISNULL(批号, '001') AS 批号,
    库存,
    是否可整包发料,
    包装数量,
	ROW_NUMBER()OVER(PARTITION BY #库存表.物料 ORDER BY ISNULL(批号, 'A001')) rn
FROM
    #库存表
    JOIN
        #物料表
            ON #物料表.物料 = #库存表.物料
),cteb AS (
SELECT
    ctea.*,
    #需要发料表.需要发料数量 - ctea.库存 AS tempkc,
	CASE WHEN #需要发料表.需要发料数量 - ctea.库存>0 THEN ctea.库存 ELSE #需要发料表.需要发料数量 END  发料数量,
	CASE WHEN #需要发料表.需要发料数量 - ctea.库存>0 THEN ctea.库存 
	WHEN ctea.是否可整包发料=0 THEN #需要发料表.需要发料数量
	ELSE 
	(CASE WHEN #需要发料表.需要发料数量%ctea.包装数量=0 THEN #需要发料表.需要发料数量 ELSE ((#需要发料表.需要发料数量/ctea.包装数量)+1)*ctea.包装数量 END)
	END  实发数量
FROM
    #需要发料表
    JOIN
        ctea
            ON ctea.物料 = #需要发料表.物料
               AND ctea.rn = 1
UNION ALL
SELECT ctea.*,cteb.tempkc-ctea.库存 AS tempkc, 
CASE WHEN tempkc - ctea.库存>0 THEN ctea.库存 ELSE tempkc END  发料数量,
CASE WHEN tempkc - ctea.库存>0 THEN ctea.库存 
WHEN ctea.是否可整包发料=0 THEN tempkc
ELSE 
(CASE WHEN tempkc %ctea.包装数量=0 THEN tempkc  ELSE ((tempkc /ctea.包装数量)+1)*ctea.包装数量 END)
END  实发数量
FROM cteb JOIN ctea ON ctea.物料 = cteb.物料 AND ctea.rn = cteb.rn+1 AND cteb.tempkc>0
)
SELECT
    cteb.物料,
    CASE
        WHEN cteb.批号 = '001'
            THEN ''
        ELSE
            cteb.批号
    END                   批号,
    cteb.实发数量 - cteb.发料数量 AS 发料数量
FROM
    cteb
WHERE
    cteb.实发数量 > cteb.发料数量
ORDER BY
    cteb.物料,
    cteb.批号;

3、

;WITH ctea AS (
SELECT
    #库存表.物料,
    ISNULL(批号, '001') AS 批号,
    库存,
    是否可整包发料,
    包装数量,
	ROW_NUMBER()OVER(PARTITION BY #库存表.物料 ORDER BY ISNULL(批号, 'A001')) rn
FROM
    #库存表
    JOIN
        #物料表
            ON #物料表.物料 = #库存表.物料
),cteb AS (
SELECT
    ctea.*,
    #需要发料表.需要发料数量 - ctea.库存 AS tempkc,
	CASE WHEN #需要发料表.需要发料数量 - ctea.库存>0 THEN ctea.库存 ELSE #需要发料表.需要发料数量 END  发料数量,
	CASE WHEN #需要发料表.需要发料数量 - ctea.库存>0 THEN ctea.库存 
	WHEN ctea.是否可整包发料=0 THEN #需要发料表.需要发料数量
	ELSE 
	(CASE WHEN #需要发料表.需要发料数量%ctea.包装数量=0 THEN #需要发料表.需要发料数量 ELSE ((#需要发料表.需要发料数量/ctea.包装数量)+1)*ctea.包装数量 END)
	END  实发数量
FROM
    #需要发料表
    JOIN
        ctea
            ON ctea.物料 = #需要发料表.物料
               AND ctea.rn = 1
UNION ALL
SELECT ctea.*,cteb.tempkc-ctea.库存 AS tempkc, 
CASE WHEN tempkc - ctea.库存>0 THEN ctea.库存 ELSE tempkc END  发料数量,
CASE WHEN tempkc - ctea.库存>0 THEN ctea.库存 
WHEN ctea.是否可整包发料=0 THEN tempkc
ELSE 
(CASE WHEN tempkc %ctea.包装数量=0 THEN tempkc  ELSE ((tempkc /ctea.包装数量)+1)*ctea.包装数量 END)
END  实发数量
FROM cteb JOIN ctea ON ctea.物料 = cteb.物料 AND ctea.rn = cteb.rn+1 AND cteb.tempkc>0
),ctec AS (
SELECT 物料,SUM(cteb.发料数量) AS 发料数量,SUM(实发数量) AS 实发数量 FROM cteb GROUP BY cteb.物料
)
SELECT
    #需要发料表.物料,
    需要发料数量,
    ctec.发料数量,
    ctec.实发数量,
    ctec.实发数量 - ctec.发料数量 AS 多发
FROM
    #需要发料表
    LEFT JOIN
        ctec
            ON ctec.物料 = #需要发料表.物料;

[weisai]

引用 1 楼 二月十六 的回复:

C003 为啥是35？c要75，c001 20 ,c002 25,还差30，c003应该发40，然后多10，为啥是发35多5？

哦，我写错了

[二月十六]

引用 4 楼 weisai 的回复：

[quote=引用 1 楼 二月十六 的回复:]
C003 为啥是35？c要75，c001 20 ,c002 25,还差30，c003应该发40，然后多10，为啥是发35多5？

哦，我写错了[/quote]嗯看看上边的对不

[weisai]

引用 5 楼 二月十六 的回复:

[quote=引用 4 楼 weisai 的回复：][quote=引用 1 楼 二月十六 的回复:]
C003 为啥是35？c要75，c001 20 ,c002 25,还差30，c003应该发40，然后多10，为啥是发35多5？

哦，我写错了[/quote]嗯看看上边的对不[/quote]

抱歉，需求有点改变，我加分

改变的地方：
需要发料表
物料 需要发料数量（总数）
A 60
B 50
C 75
D 110
E 60

改成
需要发料表
物料 需求日期 需要发料数量（总数）
A 8-1 10
A 8-2 40
B 8-1 10
B 8-2 30
B 8-3 10
C 8-1 30
C 8-2 40
C 8-3 5
D 8-1 100
D 8-2 10
E 8-1 25
E 8-2 30
E 8-3 5

要求的第一个结果也需要改为
1、发料结果（批号按批号的顺序，先进先出）
物料 需求日期 批号 发料数量
A 8-1 10
A 8-2 10
B 8-1 B001 10
B 8-2 B001 10
B 8-2 B002 10
B 8-2 B003 10
B 8-3 B003 10
C 8-1 C001 20
C 8-1 C002 10
C 8-2 C002 15
C 8-2 C003 25
C 8-3 C003 5
D 8-1 100
D 8-2 10
E 8-1 E001 5
E 8-1 E002 15

[weisai]

要求结果2需要改为
2 多发结果
物料 批号 发料数量
C C003 5
D 90

解析一下，这个多发按总数计算，比如C ，需求总数是75 ，包装数量是20 ，那么多发（库存足够的情况下）就是 20*4-75
D是 100*2-110=90

3 汇总结果
3 汇总结果
物料 需发 发料（总） 实发数量 多发
A 60 20 20 0
B 50 50 50 0
C 75 75 80 5
D 110 110 200 90
E 60 15 15 0

[二月十六]

引用 10 楼 weisai 的回复：

[quote=引用 9 楼 二月十六 的回复:]
第一个变更后的，很难实现，之前可以可着一遍递归循环，现在需要两边循环判断？楼主可以再开个帖子，看看其他人有什么好方法不。
第二个结果也不是很正常，但是可以通过第三个或者其他方法得到。

第一个结果，是否可以先把需要发料表 汇总回第一楼的总数，然后根据总安排数，再从需要发料表 按批号先后 展开 [/quote]汇总好弄，展开的时候比较慢……展开后对应消费的是多个，可能本次够消费的还要留下剩下的给下次，不够的就得把消费的剩下留给下次，比较困难……

[二月十六]

;WITH ctea AS (
SELECT 物料,SUM(库存) AS 库存 FROM #库存表 GROUP BY 物料
),cteb AS (
SELECT ctea.*,是否可整包发料,包装数量 FROM ctea JOIN #物料表 ON #物料表.物料 = ctea.物料
),ctec AS (
SELECT 物料,SUM(需要发料数量) AS 需要发料数量 FROM #需要发料表 GROUP BY 物料
),cted AS (
SELECT
    cteb.物料,
    ctec.需要发料数量,
    CASE
        WHEN ctec.需要发料数量 >= cteb.库存
            THEN cteb.库存
        ELSE
            ctec.需要发料数量
    END AS 发料,
	CASE
        WHEN ctec.需要发料数量 >= cteb.库存
            THEN cteb.库存
       WHEN 是否可整包发料=0 THEN ctec.需要发料数量
    ELSE 
    (CASE WHEN ctec.需要发料数量%包装数量=0 THEN ctec.需要发料数量 ELSE ((ctec.需要发料数量/包装数量)+1)*包装数量 END)
   
    END AS 实发数量
FROM
    ctec
    JOIN
        cteb
            ON cteb.物料 = ctec.物料
)
SELECT *,cted.实发数量-cted.发料 AS 多发 FROM cted

[二月十六]

第一个变更后的，很难实现，之前可以可着一遍递归循环，现在需要两边循环判断？楼主可以再开个帖子，看看其他人有什么好方法不。 第二个结果也不是很正常，但是可以通过第三个或者其他方法得到。

[weisai]

引用 9 楼 二月十六 的回复:

第一个变更后的，很难实现，之前可以可着一遍递归循环，现在需要两边循环判断？楼主可以再开个帖子，看看其他人有什么好方法不。
第二个结果也不是很正常，但是可以通过第三个或者其他方法得到。

第一个结果，是否可以先把需要发料表 汇总回第一楼的总数，然后根据总安排数，再从需要发料表 按批号先后 展开