求个方案~在线等
现有两表
表一
ID, RQ, NUM

A 2007.01.01

A 2007.01.02

A 2007.01.05

A 2007.01.06

B 2007.01.01

......

每个ID 没有重复的日期

表二
ID, SUM
A 40

B 62.5
表二中 ID 无重复

要求将SUM 分配到每一天去,NUM 尽量最小,尽量不要有小数.SUM 必须分配完.要求用存储过程实现


[解决办法]
create table A(ID varchar(10),RQ datetime,NUM Numeric(5,1))
insert into A select 'A ', '2007.01.01 ',NULL
insert into A select 'A ', '2007.01.02 ',NULL
insert into A select 'A ', '2007.01.05 ',NULL
insert into A select 'A ', '2007.01.06 ',NULL
insert into A select 'B ', '2007.01.01 ',NULL

create table B(ID varchar(10),SUM Numeric(5,1))
insert into B select 'A ',40
insert into B select 'B ',62.5
go

create procedure sp_test
as
begin

update m
set
NUM=cast(n.[SUM]/(select count(*) from A where ID=m.ID) as int)
from
A m,B n
where
m.ID=n.ID

update m
set
NUM=m.NUM + n.[SUM] - (select sum(NUM) from A where ID=m.ID)
from
A m,B n
where
m.ID=n.ID
and
not exists(select 1 from A where ID=m.ID and RQ> m.RQ)
end
go

exec sp_test
go

select * from A
go
/*
ID RQ NUM
---------- ------------------------------------------------------ -------
A 2007-01-01 00:00:00.000 10.0
A 2007-01-02 00:00:00.000 10.0
A 2007-01-05 00:00:00.000 10.0
A 2007-01-06 00:00:00.000 10.0
B 2007-01-01 00:00:00.000 62.5
*/

drop procedure sp_test
drop table A,B
go