update 问题 在线等

Question

update a set sum_num=b.sum_num,count_id=b.count_id
from table1 a,(
select id,count_id=count(id),sum_num=sum(num)
from table2
group by id
)b where a.id=b.id

Answer 1

在 table1 上用简单的一个 update 触发器就可以了啊

Answer 2

update a set sum_num=b.sum_num,count_id=b.count_id
from table1 a join(
select id,count_id=count(id),sum_num=sum(num)
from table2
group by id)b 
on a.id=b.id

Answer 3

补充一下
最后生成的表是
table1
id   name  sum_num   count_id 
1     a       10         3
2     b        0         0
3     c        6         2
4     d        0         0 
5     e        0         0

Answer 4

修改一下要用 left join:update a set sum_num=isnull(b.sum_num,0),count_id=isnull(b.count_id,0)
from table1 a left join(
select id,count_id=count(id),sum_num=sum(num)
from table2
group by id)b 
on a.id=b.id

Answer 5

update a set sum_num=isnull(b.sum_num,0),count_id=isnull(b.count_id,0)
from table1 a left join(
select id,count_id=count(id),sum_num=sum(num)
from table2
group by id)b 
on a.id=b.id
go
select * from table1

Answer 6

update table1 set sum_num=(select sum(num) from table2 where id=table1.id)
                 ,count_id=(select count(id) from table2 where id=table1.id)
from table2