我这里有两个表, [A] ID int(自动编号) UserID int PostID int result int [b] userID point 我现在想用一个存储过程实现下面的功能 从A表里查询postid=某个数值的记录, 然后把这些记录的result加到[b]表对应的相同的USERID的point中 在线等待,解决就给分
--写成处理的存储过程
create proc p_process
@PostID int
as
set xact_abort on
begin tran --用事务处理
--更新已经有的
update b set point=isnull(b.point,0)+isnull(a.result,0)
from b join(
select userid,result=sum(result) from A表
where postid=@PostID
group by result
)a on a.userid=b.userid
--插入不存在的
insert b(userid,point)
select a.*
from(
select userid,result=sum(result) from A表
where postid=@PostID
group by result
)a left join b on a.userid=b.userid
where b.userid is null
commit tran
go
--更新已经有的
update b set point=isnull(b.point,0)+isnull(a.result,0)
from b join(
select userid,,result=sum(result) from A表
where postid=某个数值
group by result
)a on a.userid=b.userid
--插入不存在的
insert b(userid,point)
select a.*
from(
select userid,result=sum(result) from A表
where postid=某个数值
group by result
)a left join b on a.userid=b.userid
where b.userid is null
--写成处理的存储过程
create proc p_process
@PostID int
as
set xact_abort on
begin tran --用事务处理
--更新已经有的
update b set point=isnull(b.point,0)+isnull(a.result,0)
from b join(
select userid,result from A表
where postid=@PostID
group by result
)a on a.userid=b.userid
--插入不存在的
insert b(userid,point)
select a.*
from(
select userid,result from A表
where postid=@PostID
group by result
)a left join b on a.userid=b.userid
where b.userid is null
commit tran
go
--更新已经有的
update b set point=isnull(b.point,0)+isnull(a.result,0)
from b join(
select userid,result from A表
where postid=某个数值
group by result
)a on a.userid=b.userid
--插入不存在的
insert b(userid,point)
select a.*
from(
select userid,result from A表
where postid=某个数值
group by result
)a left join b on a.userid=b.userid
where b.userid is null