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',NULLcreate table B(ID varchar(10),SUM Numeric(5,1))
insert into B select 'A',40
insert into B select 'B',62.5
gocreate 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
goexec sp_test
goselect * 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
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',NULLcreate table B(ID varchar(10),SUM Numeric(5,1))
insert into B select 'A',40
insert into B select 'B',62.5
gocreate 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
goexec sp_test
goselect * 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
ID, RQ, NUM QJA 2007.01.01 1A 2007.01.02 A 2007.01.05 A 2007.01.06
B 2007.01.01 还有个问题需要考虑就是在表一中QJ字段中数值大于0 则不参与分配,就是NUM一定为0
insert into A select 'A','2007.01.01',NULL,1
insert into A select 'A','2007.01.02',NULL,0
insert into A select 'A','2007.01.05',NULL,0
insert into A select 'A','2007.01.06',NULL,0
insert into A select 'B','2007.01.01',NULL,0create table B(ID varchar(10),SUM Numeric(5,1))
insert into B select 'A',40
insert into B select 'B',62.5
gocreate procedure sp_test
as
begin update m
set
NUM=cast(n.[SUM]/(select count(*) from A where ID=m.ID and DJ=0) as int)
from
A m,B n
where
m.ID=n.ID and m.DJ=0
update A set NUM=0 where DJ!=0
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
m.DJ=0
and
not exists(select 1 from A where ID=m.ID and DJ=0 and RQ>m.RQ)
end
goexec sp_test
goselect * from A
go
/*
ID RQ NUM DJ
---------- ------------------------------------------------------ ------- -----------
A 2007-01-01 00:00:00.000 0.0 1
A 2007-01-02 00:00:00.000 13.0 0
A 2007-01-05 00:00:00.000 13.0 0
A 2007-01-06 00:00:00.000 14.0 0
B 2007-01-01 00:00:00.000 62.5 0
*/drop procedure sp_test
drop table A,B
go
insert into A select 'A','2007.01.01',NULL,1
insert into A select 'A','2007.01.02',NULL,0
insert into A select 'A','2007.01.05',NULL,0
insert into A select 'A','2007.01.06',NULL,0
insert into A select 'B','2007.01.01',NULL,0create table B(ID varchar(10),SUM Numeric(5,1))
insert into B select 'A',40
insert into B select 'B',62.5
gocreate procedure sp_test
as
begin update m
set
NUM=n.[SUM]/(select count(*) from A where ID=m.ID and DJ=0)
from
A m,B n
where
m.ID=n.ID and m.DJ=0
update A set NUM=0 where DJ!=0
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
m.DJ=0
and
not exists(select 1 from A where ID=m.ID and DJ=0 and RQ>m.RQ)
end
goexec sp_test
goselect * from A
go
/*
ID RQ NUM DJ
---------- ------------------------------------------------------ ------- -----------
A 2007-01-01 00:00:00.000 0.0 1
A 2007-01-02 00:00:00.000 13.3 0
A 2007-01-05 00:00:00.000 13.3 0
A 2007-01-06 00:00:00.000 13.4 0
B 2007-01-01 00:00:00.000 62.5 0
*/drop procedure sp_test
drop table A,B
go
ID RQ NUM DJ
---------- ------------------------------------------------------ ------- -----------
A 2007-01-01 00:00:00.000 0.0 1
A 2007-01-02 00:00:00.000 13.0 0
A 2007-01-05 00:00:00.000 13.0 0
A 2007-01-06 00:00:00.000 14.0 0
B 2007-01-01 00:00:00.000 62.5 0
*/
这样会不会更好