update userTable set ujifen=ujifen + huitieTable.ujifen from userTable join (select uid,ujifen=sum(ujifen) from huitieTable group by uid) t on userTable .uid=t.uid
--下午都写好了的 ---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-06-29 16:39:13 ---------------------------------------------------------------- --> 测试数据:[tb1] if object_id('[tb1]') is not null drop table [tb1] create table [tb1]([uid] int,[ujifen] int) insert [tb1] select 1,100 union all select 2,0 --> 测试数据:[tb2] if object_id('[tb2]') is not null drop table [tb2] create table [tb2]([tid] int,[uid] int,[ujifen] int) insert [tb2] select 1,1,5 union all select 2,1,10 union all select 3,2,20 --------------开始查询-------------------------- update tb1 set tb1.ujifen=tb1.ujifen + (select sum(tb2.ujifen) from tb2 where tb2.uid=1 group by tb2.uid) from tb1 left join tb2 on tb1.uid=tb2.tid select * from [tb1]----------------结果---------------------------- /*uid ujifen ----------- ----------- 1 115 2 15(所影响的行数为 2 行)*/
update userTable set ujifen=ujifen + a.ujifen from userTable right join (Select uid,sum(ujiefen) as ujifen from huitieTable group by uid) a on userTable .uid=a.uid
select uid,SUM(ujifen) as ujifen into #tab_b from tab_b update tab_a set ujifen=ujifen+#tab_b.ujifen from tab_a join #tab_b on tab_a.uid=#tab_b.uid
from userTable join (select uid,ujifen=sum(ujifen) from huitieTable group by uid) t on userTable .uid=t.uid
--用户信息表:
DECLARE @T1 TABLE([uid] INT,ujifen INT)
INSERT @T1 SELECT 1 , 100
INSERT @T1 SELECT 2, 0
--用户回帖表
DECLARE @T2 TABLE(tid INT, [uid] INT,ujifen INT)
INSERT @T2 SELECT 1 , 1, 5
INSERT @T2 SELECT 2 , 1 , 10
INSERT @T2 SELECT 3, 2 , 20
UPDATE T1 SET ujifen=ujifen+(SELECT SUM(ujifen)FROM @T2 WHERE [uid]=T1.[uid])
FROM @T1 T1
SELECT * FROM @T1
/*uid ujifen
----------- -----------
1 115
2 20*/
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-06-29 16:39:13
----------------------------------------------------------------
--> 测试数据:[tb1]
if object_id('[tb1]') is not null drop table [tb1]
create table [tb1]([uid] int,[ujifen] int)
insert [tb1]
select 1,100 union all
select 2,0
--> 测试数据:[tb2]
if object_id('[tb2]') is not null drop table [tb2]
create table [tb2]([tid] int,[uid] int,[ujifen] int)
insert [tb2]
select 1,1,5 union all
select 2,1,10 union all
select 3,2,20
--------------开始查询--------------------------
update tb1 set
tb1.ujifen=tb1.ujifen + (select sum(tb2.ujifen) from tb2 where tb2.uid=1 group by tb2.uid)
from tb1
left join tb2 on tb1.uid=tb2.tid
select * from [tb1]----------------结果----------------------------
/*uid ujifen
----------- -----------
1 115
2 15(所影响的行数为 2 行)*/
update userTable
set ujifen=ujifen + a.ujifen
from userTable right join (Select uid,sum(ujiefen) as ujifen from huitieTable group by uid) a on userTable .uid=a.uid
select uid,SUM(ujifen) as ujifen into #tab_b from tab_b
update tab_a
set ujifen=ujifen+#tab_b.ujifen
from tab_a join #tab_b on tab_a.uid=#tab_b.uid