数量分摊
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)
*/
