数量分摊

DECLARE @t TABLE(ITEM NVARCHAR(50),QTY INT)

INSERT INTO @t
SELECT 'A',0 UNION ALL
SELECT 'B',0 UNION ALL
SELECT 'C',0 UNION ALL
SELECT 'D',0 UNION ALL
SELECT 'E',0

SELECT * FROM @t 

DECLARE @Amount INT;
SET @Amount=5000;  
--数据分摊 金额先取一半，除以记录条数+随机数 记录可能会增加
--需要(5000/2)/5记录数+随机数 合计=5000
SELECT 'A',500+219 UNION ALL
SELECT 'B',500+ UNION ALL
SELECT 'C',500+ UNION ALL
SELECT 'D',500+ UNION ALL
SELECT 'E',500+

--功能是否实现希望大家给点思路，金额和记录数都可能变化

[解决办法]

create table t(ITEM NVARCHAR(50),QTY INT)
 
INSERT INTO t
SELECT 'A',0 UNION ALL
SELECT 'B',0 UNION ALL
SELECT 'C',0 UNION ALL
SELECT 'D',0 UNION ALL
SELECT 'E',0
 

declare @Amount int,@ITEM nvarchar(50),@j int
select @Amount=5000,@j=count(1) from t

declare ap scroll cursor for
 select ITEM from t

open ap
fetch first from ap into @ITEM

while(@@fetch_status<>-1)
begin
 update t
  set QTY=case when @ITEM<(select max(ITEM) from t) then
            (@Amount/2)/@j+cast(rand()*(@Amount/2-isnull((select sum(QTY)-(@Amount/2)/@j*count(1) from t where ITEM<@ITEM),0)) as int)
          else
            @Amount-(select sum(QTY) from t where ITEM<@ITEM) end
  where ITEM=@ITEM

 fetch next from ap into @ITEM
end

close ap
deallocate ap


-- 结果
select * from t

/*
ITEM                                               QTY 
 
-------------------------------------------------- -----------
A                                                  745
B                                                  2514
C                                                  731
D                                                  502
E                                                  508

(5 row(s) affected)
*/


-- 验算
select sum(qty) 'Amount' from t 

/*
Amount
-----------
5000

(1 row(s) affected)
*/