怎样用用SQL 语句做 商品需求与供应的拆分明细

[ChinaITOldMan]

现有一客户商品需求信息表，其中的客户ID、商品代码、颜色与需求优先级为组合唯一字段，此表记录客户需要的商品信息，同一客户同一颜色的商品代码若需求优先级别数字越小表示要越要优先供货；

另有一客户商品供应信息表，其中客户ID、商品代码、颜色与供货优先级为组合唯一字段，此表记录按优先级可供应商品信息，如按优先级可提供某一颜色的商品给某一客户的数据，其中的供货优先级表示最快可以提供的商品的优先级，供货优先级别数字越小表示供货(出货)的顺序。

供货与出货规则：
供货时，同一客户ID、同一商品代码、同一颜色的商品先给优先级别越小的(就是数字小)就越优先供货，当优先级为1的供应完再给优先级2、3、4...的供货
出货时，同一客户ID、同一商品代码、同一颜色的商品先给优先级别越小的(就是数字小)就越优先出货，当优先级为1的出货完再给优先级2、3、4...的出货

如：商品需求信息表的第一批次需要只需要100时，而我们供货批次可以提供150时，这样150中的50可以再给其他批次的需求信息表中供货，
若商品需求信息表的第一批次需要需要100时，而我们供货批次可以提供80时，我们还要用客户商品供应信息表中其他批次给它供货（如果客户商品供应信息表中还有此商品的话）

逻辑描述有点麻烦，可以参考中左边的表与右的结果，谢谢指点！

[hgwyl]

实际业务流程是怎么操作先
1、供方在使用系统时，直接由系统处理并反馈给供方。（需方一样）
2、供、需双方仅在系统中登记信息，某个时间节点由管理员进行操作

感觉你把这个弄弄清楚，代码也差不多出来了

[Hello World,]

这个类似于库存管理（存货核算）的先进先出算法，用SQL来做的话可以先生成一个交叉表，按优先级排好序，再用变量计算（更新）一次，再选择供需不为0的数据即可

[ChinaITOldMan]

难点是主要根据需求表与供应表的优先进行拆分，麻烦大虾指点 由于上个截图可能会显示不全，重新传下截图

[Hello World,]

代码有点长，效率可能不太好：

DECLARE @Xu TABLE(CusId VARCHAR(10) NOT NULL,Code VARCHAR(10) NOT NULL,Color VARCHAR(10) NOT NULL,Qty INT NOT NULL, Seq INT NOT NULL)
DECLARE @Gong TABLE(CusId VARCHAR(10) NOT NULL,Code VARCHAR(10) NOT NULL,Color VARCHAR(10) NOT NULL,Qty INT NOT NULL, Seq INT NOT NULL)
INSERT @Xu(CusId, Code, Color, Qty, Seq)
VALUES('HW','A','Red',100,1),
('HW','A','Red',60,2),
('HW','A','Red',55,3),
('HW','A','Red',66,4),
('HW','A','Blue',9,1),
('XW','B','Red',30,1),
('XW','B','Red',50,2),
('XH','C','Blue',100,1)
INSERT @Gong(CusId, Code, Color, Qty, Seq)
VALUES('HW','A','Red',150,1),
('HW','A','Red',80,2),
('HW','A','Red',60,3),
('HW','A','Blue',11,1),
('XW','B','Red',22,1),
('XW','B','Red',33,2)

DECLARE @result TABLE(CusId VARCHAR(10) NOT NULL,Code VARCHAR(10) NOT NULL,Color VARCHAR(10) NOT NULL,Qty INT NOT NULL, Seq INT NOT NULL,
CusId2 VARCHAR(10) NOT NULL,Code2 VARCHAR(10) NOT NULL,Color2 VARCHAR(10) NOT NULL,Qty2 INT NOT NULL, Seq2 INT NOT NULL,assignQty INT NOT null)

DECLARE @CusId VARCHAR(10),@Code VARCHAR(10),@Color VARCHAR(10),@Seq INT, @Qty INT,@remainQty INT
DECLARE @CusId2 VARCHAR(10),@Code2 VARCHAR(10),@Color2 VARCHAR(10),@Seq2 INT, @Qty2 INT, @remainQty2 INT
DECLARE @assignQty INT = 0
SELECT @Color='',@CusId='',@Qty=0,@remainQty=0,@Code='',@Seq=0
SELECT @CusId2='',@Code2='',@Color2='',@Seq2=0,@remainQty2=0,@Qty2=0
DECLARE @bNeedXu bit=1
DECLARE cursor_xu CURSOR FOR --定义游标
    SELECT CusId, Code, Color, Qty, Seq FROM @Xu
    ORDER BY CusId,code,Color,Seq
OPEN cursor_xu --打开游标
FETCH NEXT FROM cursor_xu INTO  @CusId, @Code, @Color, @Qty, @Seq  --抓取下一行游标数据
WHILE @@FETCH_STATUS = 0
    BEGIN
		IF @bNeedXu=1
		BEGIN
			if exists(select * from master..syscursors where cursor_name='cursor_gong')
				begin
					CLOSE cursor_gong --关闭游标
					DEALLOCATE cursor_gong --释放游标
				end
			DECLARE cursor_gong CURSOR FOR
			SELECT CusId, Code, Color, Qty, Seq FROM @Gong WHERE CusId=@CusId AND code = @code AND color =@color ORDER BY Seq
			OPEN cursor_gong
			FETCH NEXT FROM cursor_gong INTO @CusId2,@Code2,@Color2,@Qty2,@Seq2
			SET @remainQty = @Qty
			SET @remainQty2 = @Qty2
		end
		WHILE @@FETCH_STATUS=0
			BEGIN
				IF @remainQty>0
					BEGIN
						IF @remainQty<=@remainQty2	--如果满足需求
							BEGIN
								SET @assignQty = @remainQty
								SET @remainQty2 = @remainQty2 - @assignQty
								SET @remainQty = 0
								INSERT @result(CusId, Code, Color, Qty, Seq, CusId2, Code2, Color2, Qty2,Seq2,assignQty)
								VALUES(@CusId , @Code, @Color , @Qty , @Seq , @CusId2, @Code2 , @Color2,@Qty2, @Seq2, @assignQty)
							END
						ELSE	--不能满足需求
							BEGIN
								SET @assignQty = @remainQty2
								SET @remainQty = @remainQty - @assignQty
								INSERT @result(CusId, Code, Color, Qty, Seq, CusId2, Code2, Color2, Qty2,Seq2,assignQty)
								VALUES(@CusId , @Code, @Color , @Qty , @Seq , @CusId2, @Code2 , @Color2,@Qty2, @Seq2, @assignQty)

								FETCH NEXT FROM cursor_gong INTO @CusId2,@Code2,@Color2,@Qty2,@Seq2
								SET @remainQty2 = @Qty2
							end
					END
				ELSE
					GOTO NextXu
			END
NextXu:
			IF @remainQty2=0
				SET @bNeedXu=1
			ELSE
				SET @bNeedXu = 0
			--如果可供量为0，需求不为0则写入
			IF @remainQty>0 AND @@FETCH_STATUS =-1
				BEGIN
					INSERT @result(CusId, Code, Color, Qty, Seq, CusId2, Code2, Color2, Qty2,Seq2,assignQty)
					VALUES(@CusId , @Code, @Color , @Qty , @Seq , '', '' , '',0, 0, 0)
				END 
			FETCH NEXT FROM cursor_xu INTO  @CusId, @Code, @Color, @Qty, @Seq
			SET @remainQty = @Qty
			IF @CusId<>@CusId2 OR @Code<>@Code2 OR @Color<>@Color2
				SET @bNeedXu=1
    END
CLOSE cursor_xu --关闭游标
DEALLOCATE cursor_xu --释放游标

SELECT * FROM @result --ORDER BY CusId,Code,Color,Seq,Seq2

结果：

[ChinaITOldMan]

楼上的，谢谢您耐心的指导，用cursor当时感觉也是比较好理解的，若是在生产数据库中，当数据量很大的情况下可能性能有问题；不知道若没有不用cursor的写法，谢谢！

[ChinaITOldMan]

有哪位大侠能否指点下，谢谢！

[ChinaITOldMan]

再次谢谢楼上的帮助！