大大帮忙写下这个数量分配的sql

Question

Size                   S M L
QtySales                   10 6 2
QtySuggest(建议数量) 6 10 2
QtyRemain(Warehouse Qty) 12 7 3
QtyNeed(实际分的量) 6 7 2
第一次分配          6+1=7 7 2+1=3
第二次分配          7+1=8 7 3现在需要把QtySuggest(建议数量)-QtyRemain(Warehouse Qty) 比如上面数据M尺码的这个数量是3。然后把3分配到其他尺码。分配的优先级按QtySales的数量，如果一个Size超过了QtyRemain就不分配了。 
表TbMissQty中是QtyRemain-QtySuggest<0的数据 也就是需要分配的数据,把这些数据分配到TbCurrent，一次分配一个直到missqty 为0。create table TbMissQty
(store_code_id int --门店
,product_id int --产品
,color_id int --颜色
,size_id int --尺码
,missqty int --需要去补的数量
,mid  int identity(1,1)
)
insert into TbMissQty
select 2,1021,10,2,3create table TbCurrent
(
store_code_id int, --门店
product_id int, --产品
color_id int, --颜色
size_id int, --尺码
qtysale int, --销售数量
qtyneed int, --已经补货
qtyremain int --仓库库存
)insert into TbCurrent
select 2,1021,10,1,10,6,12
union 
select 2,1021,10,3,2,2,3

Answer 1

上面那个不看也可以的 只要TbMissQty每一行的数据分配到TbCurrent 条件是store_code_id product_id color_id

Answer 2

create table TbMissQty
(store_code_id int --门店
,product_id int --产品
,color_id int --颜色
,size_id int --尺码
,missqty int --需要去补的数量
,mid  int identity(1,1)
)
insert into TbMissQty
select 2,1021,10,2,3create table TbCurrent
(
store_code_id int, --门店
product_id int, --产品
color_id int, --颜色
size_id int, --尺码
qtysale int, --销售数量
qtyneed int, --已经补货
qtyremain int --仓库库存
)
delete from TbCurrent
insert into TbCurrent
select 2,1021,10,1,10,6,12
union 
select 2,1021,10,3,2,2,3
union 
select 2,1021,10,4,2,3,3
union 
select 2,1021,10,5,2,2,3drop table #tbmissqty
select *   into #tbmissqty from tbmissqtydrop table #tbcurrentselect *  into #tbcurrent from tbcurrentdeclare @rows int,@rowCnt int,@store_code_idCur int,@product_id int,@color_id int,@size_id int,@qtyMissing int
declare @qtyneed int,@qtysale int,@qtyremain int
declare @qtyneedsumo int,@qtyneedsumn intselect @rows = count(*) from #tbmissqty           
 select @rowCnt = 1            
 while(@rowCnt <= @rows)            
 begin            
  select @store_code_idCur = store_code_id,              
    @product_id = product_id,             
    @color_id = color_id,            
    @size_id = size_id,            
    @qtyMissing = missqty            
  from #tbmissqty             
  where mid = 1  declare @rownumber int

 while(@qtyMissing)>0
 begin
set @qtyneedsumo=0 
set @qtyneedsumn=0
select @qtyneedsumo=sum(qtyneed) from #tbcurrent
select @qtyneedsumo
update #tbcurrent  set qtyneed=qtyneed+1,qtyremain=qtyremain-1 where exists(select 'X' from (
select *,ROW_NUMBER() OVER(ORDER BY qtysale DESC) AS rowseq from #tbcurrent where store_code_id=@store_code_idCur and product_id=@product_id and color_id=@color_id and
 qtyremain-qtyneed>0) as b where #tbcurrent.store_code_id=b.store_code_id and #tbcurrent.product_id=b.product_id and #tbcurrent.color_id=b.color_id and #tbcurrent.size_id=b.size_id) select @qtyneedsumn=sum(qtyneed) from #tbcurrent
select @qtyneedsumn
select @qtyneedsumo-@qtyneedsumn
select @qtyMissing=@qtyMissing-(@qtyneedsumn-@qtyneedsumo)
 end
 select @rowCnt = @rowCnt + 1   
    
end     
select * from tbcurrent
select * from #tbcurrent

Answer 3

对照你代码，想了半天，大概明白你的意思了。
if object_id('tempdb..#tbcurrent') is not null
drop table #tbcurrent
go
select * into #tbcurrent from tbcurrentdeclare @missqty int
declare @store_code_id int,@product_id int,@color_id int,@size_id int
declare @rowcnt int --受影响的行数
set @rowcnt = 1declare currow cursor for
select store_code_id,product_id,color_id,size_id,missqty from tbmissqty
open currow
fetch next from currow into @store_code_id,@product_id,@color_id,@size_id,@missqty
while @@fetch_status = 0
begin
while @missqty <> 0 and @rowcnt <> 0
begin
update #tbcurrent set qtyneed=qtyneed+1,qtyremain=qtyremain-1 
from (
select top (@missqty) store_code_id,product_id,color_id,size_id 
from #tbcurrent 
where qtyremain-qtyneed>=0 and size_id <> @size_id and store_code_id = @store_code_id
and product_id = @product_id and color_id = @color_id
Order by qtysale desc
) as a
where #tbcurrent.store_code_id=a.store_code_id and #tbcurrent.product_id=a.product_id and #tbcurrent.color_id=a.color_id and #tbcurrent.size_id=a.size_id set @rowcnt = @@rowcount
set @missqty = @missqty - @rowcnt
end fetch next from currow into @store_code_id,@product_id,@color_id,@size_id,@missqty
end
close currow
deallocate currowselect * from #tbcurrent结贴，是一种美德

Answer 4

missqty没有分完啊，最好不要游标。

Answer 5

delete from TbMissQty
insert into TbMissQty
select 2,1021,10,2,10
union 
select 2,1021,11,2,12delete from TbCurrent
insert into TbCurrent
select 2,1021,10,1,10,6,2
union 
select 2,1021,10,3,2,2,3
union 
select 2,1021,10,4,2,3,3
union 
select 2,1021,10,5,2,2,3
union 
select 2,1021,11,1,10,6,2
union 
select 2,1021,11,3,2,2,3
union 
select 2,1021,11,4,2,3,3
union 
select 2,1021,11,5,2,2,3把TbMissQty表中两行数据missqty分别是10,12 轮训分配到TbCurrent对应的store_code_id,product_id,color_id的所有size_id中去
大大门帮帮忙,我试了几种办法都没有分成功。

Answer 6

没耐心读这么多代码。。自己在前端也可用DELPHI循环完成啦

Answer 7

此处用游标，速度比你原来一行行的从missqty表中取要更快些.你看我的理解对不对：
1.tbcurrent中只有qtyremain-qtyneed>=0的记录才分。
2.如果tbcurrent中没有qtyremain-qtyneed>=0的记录了，就算missqty没分完，也不分了.
3.missqty分给tbcurrent中与之store_code_id,product_id,color_id相同，但size_id不同的记录.
以下语句是基于我的上述理解写的.如果我的理解有误，请更改代码相应部分即可。if object_id('tempdb..#tbcurrent') is not null
    drop table #tbcurrent
go
select * into #tbcurrent from tbcurrentdeclare @missqty int
declare @store_code_id int,@product_id int,@color_id int,@size_id int
declare @rowcnt int --受影响的行数
set @rowcnt = 1declare currow cursor for
    select store_code_id,product_id,color_id,size_id,missqty from tbmissqty
open currow
fetch next from currow into @store_code_id,@product_id,@color_id,@size_id,@missqty
while @@fetch_status = 0
begin
    while @missqty <> 0 and @rowcnt <> 0
    begin
        update #tbcurrent set qtyneed=qtyneed+1,qtyremain=qtyremain-1 
        from (
            select top (@missqty) store_code_id,product_id,color_id,size_id 
            from #tbcurrent 
            where qtyremain-qtyneed>=0 and size_id <> @size_id and store_code_id = @store_code_id
                and product_id = @product_id and color_id = @color_id
            Order by qtysale desc
        ) as a
        where #tbcurrent.store_code_id=a.store_code_id and #tbcurrent.product_id=a.product_id and #tbcurrent.color_id=a.color_id and #tbcurrent.size_id=a.size_id        set @rowcnt = @@rowcount
        set @missqty = @missqty - @rowcnt
    end
set @rowcnt = 1    fetch next from currow into @store_code_id,@product_id,@color_id,@size_id,@missqty
end
close currow
deallocate currow--以上代码执行后，针对你#11的演示数据，前后结果为：
select * from tbcurrent
/*
store_code_id product_id  color_id    size_id     qtysale     qtyneed     qtyremain
------------- ----------- ----------- ----------- ----------- ----------- -----------
2             1021        10          1           10          6           2
2             1021        10          3           2           2           3
2             1021        10          4           2           3           3
2             1021        10          5           2           2           3
2             1021        11          1           10          6           2
2             1021        11          3           2           2           3
2             1021        11          4           2           3           3
2             1021        11          5           2           2           3(8 行受影响)
*/
select * from #tbcurrent
/*
store_code_id product_id  color_id    size_id     qtysale     qtyneed     qtyremain
------------- ----------- ----------- ----------- ----------- ----------- -----------
2             1021        10          1           10          6           2
2             1021        10          3           2           3           2
2             1021        10          4           2           4           2
2             1021        10          5           2           3           2
2             1021        11          1           10          6           2
2             1021        11          3           2           3           2
2             1021        11          4           2           4           2
2             1021        11          5           2           3           2(8 行受影响)
*/

Answer 8

 if exists(select * from dbo.sysobjects 
           where xtype='u' AND  name LIKE 'TbMissQty')
 drop table TbMissQty
create table TbMissQty
(store_code_id int --门店
,product_id int --产品
,color_id int --颜色
,size_id int --尺码
,missqty int --需要去补的数量
,mid  int identity(1,1)
)
 if exists(select * from dbo.sysobjects 
           where xtype='u' AND  name LIKE 'TbCurrent')
 drop table TbCurrent
create table TbCurrent
(
store_code_id int, --门店
product_id int, --产品
color_id int, --颜色
size_id int, --尺码
qtysale int, --销售数量
qtyneed int, --已经补货
qtyremain int --仓库库存
)
    TRUNCATE TABLE TbMissQty
    insert into TbMissQty
    select 2,1021,10,2,10
    UNION ALL
     select 3,1022,11,2,10
   --SELECT *  FROM TbMissQty
    TRUNCATE TABLE TbCurrent
insert into TbCurrent
select 2,1021,10,1,10,6,2
union 
select 2,1021,10,3,2,2,3
union 
select 2,1021,10,4,2,3,3
union 
select 2,1021,10,5,2,2,3
-- union 
-- select 3,1022,11,5,2,2,3
-- union 
-- select 3,1022,11,6,2,2,3
--   SELECT *  FROM TbCurrent
 
declare @rows int,       @rowCnt int,     @store_code_idCur int,
        @product_id int, @color_id int,   @size_id int,   
        @qtyneed int,    @qtysale int,    @qtyremain int,
        @qtyneedsumo int,@qtyneedsumn int,@qtyMissing int
        
 select *   into #tbmissqty from tbmissqty
 select *  into #tbcurrent from tbcurrent
 -- select @rows = count(*) from #tbmissqty  
 -- SELECT @rows
 --set @rowCnt = 1 
 --SELECT @rowCnt
  select *,ROW_NUMBER() OVER(partition BY store_code_id ,product_id,color_id ORDER BY qtysale DESC) AS rowseq 
into #tempcurrent
from #tbcurrent WHERE  qtyremain>0 SELECT a.*,b.missqty INTO #temp FROM #tempcurrent a
 INNER  JOIN (SELECT store_code_id ,product_id,color_id,sum(missqty) AS missqty  FROM #tbmissqty  
GROUP BY store_code_id ,product_id,color_id) b
 ON a.store_code_id=b.store_code_id AND a.product_id=b.product_id AND a.color_id=b.color_id
 
 SELECT *  FROM #temp
 SELECT DISTINCT store_code_id ,product_id,color_id,missqty,count(*) AS rowCnt FROM #temp GROUP BY store_code_id ,product_id,color_id,missqty
 DECLARE authors_cursor CURSOR FOR    
 SELECT DISTINCT store_code_id ,product_id,color_id,missqty,count(*) AS rowCnt FROM #temp GROUP BY store_code_id ,product_id,color_id,missqty
 open authors_cursor                                                
 fetch next from authors_cursor into @store_code_idCur,@product_id,@color_id,@qtyMissing,@rowCnt
 while @@fetch_status=0                                                
BEGIN 
SET @qtyremain=1
SET @rows=1WHILE @qtyMissing>0
BEGIN

    IF @rows>@rowCnt  
BEGIN
SET @rows=1
END

UPDATE #temp 
         SET qtyneed=qtyneed+1 ,qtyremain=qtyremain-1
        WHERE store_code_id=@store_code_idCur AND product_id=@product_id AND color_id=@color_id AND @rows=rowseq AND qtyremain>0
SET @qtyMissing=@qtyMissing-1
SET @qtyremain=@qtyremain+1
IF (SELECT TOP 1 qtyremain FROM #temp WHERE rowseq=@rows)=0 AND (SELECT TOP 1 store_code_id FROM #temp WHERE rowseq=@rows)=@store_code_idCur
   AND (SELECT TOP 1 product_id FROM #temp WHERE rowseq=@rows)=@product_id 
   AND (SELECT TOP 1 color_id FROM #temp WHERE rowseq=@rows)=@color_id 
begin
SET @rows=(SELECT TOP 1 rowseq FROM #temp WHERE rowseq=@rows)+1
END
ELSE
BEGIN
SET @rows=@rows+1
END
select @qtyMissing,@rows
END
 fetch next from authors_cursor into @store_code_idCur,@product_id,@color_id,@qtyMissing,@rowCnt
END  
 CLOSE authors_cursor                              
 DEALLOCATE authors_cursor 
 SELECT *  FROM #temp 
 DROP TABLE #temp
 DROP TABLE #tempcurrent
 DROP TABLE #tbmissqty
 DROP TABLE #tbcurrent 这种还是没办法分完 唉 郁闷高人指点啊

Answer 9

谢谢coleling 百忙中帮我看这些问题
missqty的数量<= tbcurrent中qtyremain数量的时候肯定会分完，大于qtyremain 只分qtyremain的数量也就是还剩(qtyremain-missqty)不能分，因为实际中qtyremain表示的是现有库存，如果多分了库存就出现负数了。比如missqty=10 tbcurent中qtyremain=12 那此时只会分10个 如果missqty=10 qtyremai=8 只分8个

Answer 10

修正过的记过，可是感觉数据有点慢。
 if exists(select * from dbo.sysobjects 
           where xtype='u' AND  name LIKE 'TbMissQty')
 drop table TbMissQty
create table TbMissQty
(store_code_id int --门店
,product_id int --产品
,color_id int --颜色
,size_id int --尺码
,missqty int --需要去补的数量
,mid  int identity(1,1)
)
 if exists(select * from dbo.sysobjects 
           where xtype='u' AND  name LIKE 'TbCurrent')
 drop table TbCurrent
create table TbCurrent
(
store_code_id int, --门店
product_id int, --产品
color_id int, --颜色
size_id int, --尺码
qtysale int, --销售数量
qtyneed int, --已经补货
qtyremain int --仓库库存
)
        TRUNCATE TABLE TbMissQty
    insert into TbMissQty
    select 2,1021,10,2,8
union 
select 2,1021,11,2,11
   --SELECT *  FROM TbMissQty
    TRUNCATE TABLE TbCurrent
insert into TbCurrent
select 2,1021,10,1,10,6,2
union 
select 2,1021,10,3,2,2,2
union 
select 2,1021,10,4,2,3,3
union 
select 2,1021,10,5,2,2,3
union 
select 2,1021,11,1,10,3,3
union 
select 2,1021,11,3,2,2,3
union 
select 2,1021,11,4,2,3,3
union 
select 2,1021,11,5,2,2,3
 
declare @rows int,       @rowCnt int,     @store_code_idCur int,
        @product_id int, @color_id int,   @size_id int,   
        @qtyneed int,    @qtysale int,    @qtyremain int,
        @qtyneedsumo int,@qtyneedsumn int,@qtyMissing int
        
 select *   into #tbmissqty from tbmissqty
 select *  into #tbcurrent from tbcurrent
 -- select @rows = count(*) from #tbmissqty  
 -- SELECT @rows
 --set @rowCnt = 1 
 --SELECT @rowCnt
  select *,ROW_NUMBER() OVER(partition BY store_code_id ,product_id,color_id ORDER BY qtysale DESC) AS rowseq 
into #tempcurrent
from #tbcurrent WHERE  qtyremain>0
 SELECT a.*,b.missqty INTO #temp FROM #tempcurrent a
 INNER  JOIN (SELECT store_code_id ,product_id,color_id,sum(missqty) AS missqty  FROM #tbmissqty  GROUP BY store_code_id ,product_id,color_id) b
 ON a.store_code_id=b.store_code_id AND a.product_id=b.product_id AND a.color_id=b.color_id
 
 SELECT *  FROM #temp
 SELECT DISTINCT store_code_id ,product_id,color_id,missqty,count(*) AS rowCnt FROM #temp GROUP BY store_code_id ,product_id,color_id,missqty
 DECLARE authors_cursor CURSOR FOR    
 SELECT DISTINCT store_code_id ,product_id,color_id,missqty,count(*) AS rowCnt FROM #temp GROUP BY store_code_id ,product_id,color_id,missqty
 open authors_cursor                                                
 fetch next from authors_cursor into @store_code_idCur,@product_id,@color_id,@qtyMissing,@rowCnt
 while @@fetch_status=0                                                
BEGIN 
--SET @qtyremain=1
SET @rows=1
WHILE @qtyMissing>0 
BEGIN
    IF @rows>@rowCnt  
BEGIN
SET @rows=1
END
IF (SELECT TOP 1 qtyremain FROM #temp WHERE rowseq=@rows)=0 
   AND (SELECT TOP 1 store_code_id FROM #temp WHERE rowseq=@rows)=@store_code_idCur
   AND (SELECT TOP 1 product_id FROM #temp WHERE rowseq=@rows)=@product_id 
   AND (SELECT TOP 1 color_id FROM #temp WHERE rowseq=@rows)=@color_id 
begin
SET @rows=(SELECT TOP 1 rowseq FROM #temp WHERE rowseq=@rows)+1
END

UPDATE #temp 
         SET qtyneed=qtyneed+1 ,qtyremain=qtyremain-1
        WHERE store_code_id=@store_code_idCur AND product_id=@product_id AND color_id=@color_id AND @rows=rowseq AND qtyremain>0
SET @qtyMissing=@qtyMissing-1
SET @rows=(SELECT TOP 1 rowseq FROM #temp WHERE rowseq=@rows)+1
END
 fetch next from authors_cursor into @store_code_idCur,@product_id,@color_id,@qtyMissing,@rowCnt
END  
 CLOSE authors_cursor                              
 DEALLOCATE authors_cursor 
 SELECT *  FROM #temp 
 DROP TABLE #temp
 DROP TABLE #tempcurrent
 DROP TABLE #tbmissqty
 DROP TABLE #tbcurrent

Answer 11

不知你的表达有问题，还是我的理解有问题，你的话理解起来总是很费劲。如果这一次还不对的话，请你直接告诉我，以#11的演示数据，分配前后的结果是什么。
if object_id('tempdb..#tbcurrent') is not null
    drop table #tbcurrent
go
select * into #tbcurrent from tbcurrentdeclare @missqty int
declare @store_code_id int,@product_id int,@color_id int,@size_id int
declare @rowcnt int --受影响的行数
set @rowcnt = 1declare currow cursor for
    select store_code_id,product_id,color_id,size_id,missqty from tbmissqty
open currow
fetch next from currow into @store_code_id,@product_id,@color_id,@size_id,@missqty
while @@fetch_status = 0
begin
    while @missqty <> 0 and @rowcnt <> 0
    begin
        update #tbcurrent set qtyneed=qtyneed+1,qtyremain=qtyremain-1 
        from (
            select top (@missqty) store_code_id,product_id,color_id,size_id 
            from #tbcurrent 
            where qtyremain>0 and size_id <> @size_id and store_code_id = @store_code_id
                and product_id = @product_id and color_id = @color_id
            Order by qtysale desc
        ) as a
        where #tbcurrent.store_code_id=a.store_code_id and #tbcurrent.product_id=a.product_id and #tbcurrent.color_id=a.color_id and #tbcurrent.size_id=a.size_id        set @rowcnt = @@rowcount
        set @missqty = @missqty - @rowcnt
    end
    set @rowcnt = 1    fetch next from currow into @store_code_id,@product_id,@color_id,@size_id,@missqty
end
close currow
deallocate currow--以上代码执行后，针对你#11的演示数据，前后结果为：
select * from tbcurrent
/*
store_code_id product_id  color_id    size_id     qtysale     qtyneed     qtyremain
------------- ----------- ----------- ----------- ----------- ----------- -----------
2             1021        10          1           10          6           2
2             1021        10          3           2           2           3
2             1021        10          4           2           3           3
2             1021        10          5           2           2           3
2             1021        11          1           10          6           2
2             1021        11          3           2           2           3
2             1021        11          4           2           3           3
2             1021        11          5           2           2           3(8 行受影响)
*/
select * from #tbcurrent
/*
store_code_id product_id  color_id    size_id     qtysale     qtyneed     qtyremain
------------- ----------- ----------- ----------- ----------- ----------- -----------
2             1021        10          1           10          8           0
2             1021        10          3           2           5           0
2             1021        10          4           2           6           0
2             1021        10          5           2           4           1
2             1021        11          1           10          8           0
2             1021        11          3           2           5           0
2             1021        11          4           2           6           0
2             1021        11          5           2           5           0(8 行受影响)
*/
结贴，是一种美德

Answer 12

谢谢coleling
就是这样的。