求个方案~在线等
现有两表
表一
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