求一SQL语句

[noproblem12]

a表结构
id NUM
1 500
2 200
3 30
4 400

b表
id A NUM
1 1 20
1 2 50
1 3 60
2 1 10
3 1 10
...

要求将b表id与a表id相同的记录中的NUM字段相减，只操作一个id的记录
比如操作id＝1的记录后，b表不变，a表变成
id NUM
1 370
2 200
3 30
4 400
可写成存贮过程，同时用参数确定id

[noproblem12]

更改一下b表结构为
b表
id A NUM
1 1 20
1 1 20
1 1 20
1 2 50
1 2 50
1 3 60
2 1 10
3 1 10
.........
id和a列相同的忽略

[yujohny]

update a表
set NUM= NUM-T2.Num
from (select id,sum(Num) Num from (select distinct id,A,Num from b表) T1 group by id,A) T2 where T2.id = a表.id

[yujohny]

或者
update T
set T.NUM= T.NUM-T2.Num
from a表 T inner join (select id,sum(Num) Num from (select distinct id,A,Num from b表) T1 group by id,A) T2 where T2.id = T.id

[sdhdy]

update a表 set num=a表.num-isnull(b.num,0) from (select id,sum(num) num from b表 ) b
where a表.id=b表.id

[sdhdy]

update a表 set num=a表.num-isnull(b.num,0) from (select id,sum(num) num from b表 group by id ) b
where a表.id=b表.id

[sdhdy]

--modify
update a表 set num=a表.num-isnull(b.num,0) from (select id,sum(num) num from b表 group by id ) b
where a表.id=b.id

[aierong]

create table b(id int, A int, NUM int)
insert into b select 1, 1, 20
insert into b select 1, 1, 20
insert into b select 1, 1, 20
insert into b select 1, 2, 50
insert into b select 1, 2, 50
insert into b select 1, 3, 60
insert into b select 2, 1, 10
insert into b select 3, 1, 10
go
create table a(id int, NUM int)
insert into a select 1, 500
insert into a select 2, 200
insert into a select 3, 30
insert into a select 4, 400

select a.id,a.num-isnull(b.sums,0) num
from a left join (
select [id],sum(num) sums
from b
group by [id]) b
on a.[id]=b.[id]

[jkljf]

declare @a table(id int, NUM int)
declare @b table(id int, A int, NUM int)

insert into @a values(1, 500)
insert into @a values(2, 200)
insert into @a values(3, 30)
insert into @a values(4, 400)

insert into @b values(1, 1, 20)
insert into @b values(1, 1, 20)
insert into @b values(1, 1, 20)
insert into @b values(1, 2, 50)
insert into @b values(1, 2, 50)
insert into @b values(1, 3, 60)
insert into @b values(2, 1, 10)
insert into @b values(3, 1, 10)


select * from @a
select * from @b

update @a
set NUM = NUM - t.c from (select SUM(distinct b.NUM) c from @b b where b.id = 1 group by b.id ) t
where id = 1

select * from @a

[jkljf]

把 b.id = 1 和 id = 1的值换成@id的这个变量就可以了

[jkljf]

declare @a table(id int, NUM int)
declare @b table(id int, A int, NUM int)

insert into @a values(1, 500)
insert into @a values(2, 200)
insert into @a values(3, 30)
insert into @a values(4, 400)

insert into @b values(1, 1, 20)
insert into @b values(1, 1, 20)
insert into @b values(1, 1, 20)
insert into @b values(1, 2, 50)
insert into @b values(1, 2, 50)
insert into @b values(1, 3, 60)
insert into @b values(2, 1, 10)
insert into @b values(3, 1, 10)


select * from @a
select * from @b

update @a
set NUM = NUM - isnull(t.c, 0) from (select SUM(distinct b.NUM) c from @b b where b.id = 1) t
where id = 1

select * from @a

[银狐被占用]

我有点没有看明白楼主的问题！

楼主说只操作一个ID。

那么条件就为ID=1不就可以了吗！

update a set a.num=a.num-(select sum(num) num from b where b.id=1) where a.id=1

不就可以了吗！?

是不是我理解错了！？请各位大侠指教！

[jkljf]

刚才搞错了

declare @a table(id int, NUM int)
declare @b table(id int, A int, NUM int)

insert into @a values(1, 500)
insert into @a values(2, 200)
insert into @a values(3, 30)
insert into @a values(4, 400)

insert into @b values(1, 1, 20)
insert into @b values(1, 1, 20)
insert into @b values(1, 1, 20)
insert into @b values(1, 2, 50)
insert into @b values(1, 2, 50)
insert into @b values(1, 3, 60)
insert into @b values(1, 4, 20)
insert into @b values(2, 1, 10)
insert into @b values(3, 1, 10)


select * from @a
select * from @b

declare @id int
set @id = 1
update @a
set NUM = NUM - isnull(tt.cc, 0) from (select SUM(t.c) cc from (select SUM(distinct b.NUM) c from @b b where b.id = @id group by b.id, b.A) t) tt
where id = @id

select * from @a