update A
set A.余数=A.总数-B.减数
from 表1 A
left join
(select 序号,sum(减数) as 减数
from 表2
group by 序号
) as
B on B.序号=A.序号
set A.余数=A.总数-B.减数
from 表1 A
left join
(select 序号,sum(减数) as 减数
from 表2
group by 序号
) as
B on B.序号=A.序号
set A.余数=A.总数-B.减数
from 表1 A
left join
(select 序号,sum(减数) as 减数
from 表2
group by 序号
) as
B on B.序号=A.序号
create table 表1(序号 int, 总数 int, 余数 int )
insert into 表1
select 1 , 100 , 0
union
select 2 , 99 , 0
union
select 3 , 56 , 0
create table 表2(序号 int, 减数 int )
insert into 表2
select 1 , 10
union
select 1 , 20
union
select 1 , 30
union
select 2 , 40
union
select 2 , 50update A
set A.余数=A.总数-isnull(B.减数,0)
from 表1 A
left join
(select 序号,isnull(sum(减数),0) as 减数
from 表2
group by 序号
) as
B on B.序号=A.序号select * from 表1測試結果
1 100 40
2 99 9
3 56 56
set A.余数=A.总数-B.减数
from 表1 A
,(select 序号,sum(减数) as 减数
from 表2
group by 序号
) as
B where B.序号=A.序号
group by a.序号,a. 总数