表1
----------------------------------
id user data
1 ss 2
2 aa 3
3 rr 10
表2
----------------------------------
id data user
2 sdasd ss
3 21321 aa
4 wqeq ss
怎么根据在“表2”的user里用户,在“表1”的data加1
...全文
4410打赏收藏
最简单的(马上结帖)
表1 ---------------------------------- id user data 1 ss 2 2 aa 3 3 rr 10 表2 ---------------------------------- id data user 2 sdasd ss 3 21321 aa 4 wqeq ss 怎么根据在“表2”的user里用户,在“表1”的data加1
--测试数据
declare @表1 table(id int,[user] varchar(10),data int)
insert into @表1
select 1,'ss',2
union all select 2,'aa',3
union all select 3,'rr',10
declare @表2 table(id int,data varchar(10),[user] varchar(10))
insert into @表2
select 2,'sdasd','ss'
union all select 3,'21321','aa'
union all select 4,'wqeq','ss'
--更新方法2.
update @表1 set data=a.data+b.aa
from @表1 a join(select [user],aa=count(*) from @表2 group by [user]) b on a.[user]=b.[user]
--显示处理结果
select * from @表1
/*--测试结果
id user data
----------- ---------- -----------
1 ss 4
2 aa 4
3 rr 10
--测试数据
declare @表1 table(id int,[user] varchar(10),data int)
insert into @表1
select 1,'ss',2
union all select 2,'aa',3
union all select 3,'rr',10
declare @表2 table(id int,data varchar(10),[user] varchar(10))
insert into @表2
select 2,'sdasd','ss'
union all select 3,'21321','aa'
union all select 4,'wqeq','ss'
--更新方法1.
update @表1 set data=data+isnull((select count(*) from @表2 where [user]=a.[user]),0)
from @表1 a
--显示处理结果
select * from @表1
/*--测试结果
id user data
----------- ---------- -----------
1 ss 4
2 aa 4
3 rr 10