update u set u.user_ex=u.user_ex+p.c from #users u,(select post_username,c=count(1) from #post group by post_username) p where u.[user_name]=p.[post_username]
update users set user_ex= user_ex+(SELECT COUNT(*) FROM POST P WHERE P.USERNAME=U.USENAME) FROM USERS U
/* -- Author:SQL77--RICHIE -- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: users If object_id('users') is not null Drop table users Go Create table users(user_nam varchar(2),user_ex int) Go Insert into users select 'AA',1 union all select 'BB',1 union all select 'CC',1 Go --Start --Select * from users-- Test Data: post If object_id('post') is not null Drop table post Go Create table post(post_username varchar(2)) Go Insert into post select 'AA' union all select 'AA' union all select 'BB' Go update users set user_ex= user_ex+(SELECT COUNT(*) FROM POST P WHERE P.post_username=U.user_nam) FROM users U SELECT * FROM usersuser_nam user_ex -------- ----------- AA 3 BB 2 CC 1(所影响的行数为 3 行)
from #users u,(select post_username,c=count(1) from #post group by post_username) p
where u.[user_name]=p.[post_username]
user_ex+(SELECT COUNT(*) FROM POST P WHERE P.USERNAME=U.USENAME) FROM USERS U
/*
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: users
If object_id('users') is not null
Drop table users
Go
Create table users(user_nam varchar(2),user_ex int)
Go
Insert into users
select 'AA',1 union all
select 'BB',1 union all
select 'CC',1
Go
--Start
--Select * from users-- Test Data: post
If object_id('post') is not null
Drop table post
Go
Create table post(post_username varchar(2))
Go
Insert into post
select 'AA' union all
select 'AA' union all
select 'BB'
Go
update users set user_ex=
user_ex+(SELECT COUNT(*) FROM POST P WHERE P.post_username=U.user_nam) FROM users U SELECT * FROM usersuser_nam user_ex
-------- -----------
AA 3
BB 2
CC 1(所影响的行数为 3 行)