继续昨天的合并论坛用户,昨天只需要加帖子数目
今天还要加上消息,好友
消息有2张表:Message,Message_Group
Message_Group里面有:GroupName(值为“垃圾箱”“收件箱”“发件箱”“草稿箱”), UserID,MessageGroupID
Message里面有:MessageID,MessageGroupID
实现把原来用户的消息加到目标用户上,并删除原来用户. MessageGroupID改成目标用户的好友也有2张表:Friends,FriendGroup
FriendGroup里面有:GroupName(值为“我的好友”“黑名单”) ,UserID,FriendGroupID
Friends里面有:FriendID,FriendGroupID,FriendUserID
实现把原来用户的黑名单加到目标用户的黑名单。把原来用户的好友加到目标用户的好友,如果目标用户的黑名单在原来用户里面是好友,就把原来用户的好友改成目标用户的黑名单。然后删除原来的用户。最好和昨天的联合起来写个存储过程
今天还要加上消息,好友
消息有2张表:Message,Message_Group
Message_Group里面有:GroupName(值为“垃圾箱”“收件箱”“发件箱”“草稿箱”), UserID,MessageGroupID
Message里面有:MessageID,MessageGroupID
实现把原来用户的消息加到目标用户上,并删除原来用户. MessageGroupID改成目标用户的好友也有2张表:Friends,FriendGroup
FriendGroup里面有:GroupName(值为“我的好友”“黑名单”) ,UserID,FriendGroupID
Friends里面有:FriendID,FriendGroupID,FriendUserID
实现把原来用户的黑名单加到目标用户的黑名单。把原来用户的好友加到目标用户的好友,如果目标用户的黑名单在原来用户里面是好友,就把原来用户的好友改成目标用户的黑名单。然后删除原来的用户。最好和昨天的联合起来写个存储过程
@txtbox4 varchar(50),
@txtbox1 varchar(50),
@txtbox2 varchar(50)=null,
@txtbox3 varchar(50)=null
as
set nocount ondeclare @UserID4 int
select @UserID4=UserID from User_Info where UserName=@txtbox4
--检查目标用户
if @UserID4 is null
return 1 --目标用户不存在declare @UserID1 int
select @UserID1=UserID from User_Info where UserName=@txtbox1
--检查源用户1
if @UserID1 is null
return 2 --源用户1不存在if @UserID1<>@UserID4 --不相等才修改
begin
update a set TotalPosts=a.TotalPosts+b.TotalPosts
from statistics_User a,statistics_User b
where a.UserID=@UserID4 and b.UserID=@UserID1
--转消息
update a
set MessageGroupID=c.MessageGroupID
from Message a,Message_Group b,Message_Group c
where a.MessageGroupID=b.MessageGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName=c.GroupName --这里如果有自建文件夹将转不过来 --删除消息Group
delete Message_Group where UserID=@UserID1
delete statistics_User where UserID=@UserID1 delete User_Info where UserID=@UserID1
end--第二个用户
set @UserID1=null
select @UserID1=UserID from User_Info where UserName=@txtbox2if @UserID1 is not null and @UserID1<>@UserID4 --不相等才修改
begin
update a set TotalPosts=a.TotalPosts+b.TotalPosts
from statistics_User a,statistics_User b
where a.UserID=@UserID4 and b.UserID=@UserID1 --转消息
update a
set MessageGroupID=c.MessageGroupID
from Message a,Message_Group b,Message_Group c
where a.MessageGroupID=b.MessageGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName=c.GroupName --这里如果有自建文件夹将转不过来 --删除消息Group
delete Message_Group where UserID=@UserID1
delete statistics_User where UserID=@UserID1 delete User_Info where UserID=@UserID1
end--第三个用户
set @UserID1=null
select @UserID1=UserID from User_Info where UserName=@txtbox3if @UserID1 is not null and @UserID1<>@UserID4 --不相等才修改
begin
update a set TotalPosts=a.TotalPosts+b.TotalPosts
from statistics_User a,statistics_User b
where a.UserID=@UserID4 and b.UserID=@UserID1 --转消息
update a
set MessageGroupID=c.MessageGroupID
from Message a,Message_Group b,Message_Group c
where a.MessageGroupID=b.MessageGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName=c.GroupName --这里如果有自建文件夹将转不过来 --删除消息Group
delete Message_Group where UserID=@UserID1
delete statistics_User where UserID=@UserID1 delete User_Info where UserID=@UserID1
endgo
@txtbox4 varchar(50),
@txtbox1 varchar(50),
@txtbox2 varchar(50)=null,
@txtbox3 varchar(50)=null
as
set nocount ondeclare @UserID4 int
select @UserID4=UserID from User_Info where UserName=@txtbox4
--检查目标用户
if @UserID4 is null
return 1 --目标用户不存在declare @UserID1 int
select @UserID1=UserID from User_Info where UserName=@txtbox1
--检查源用户1
if @UserID1 is null
return 2 --源用户1不存在if @UserID1<>@UserID4 --不相等才修改
begin
update a set TotalPosts=a.TotalPosts+b.TotalPosts
from statistics_User a,statistics_User b
where a.UserID=@UserID4 and b.UserID=@UserID1
--转消息
update a
set MessageGroupID=c.MessageGroupID
from Message a,Message_Group b,Message_Group c
where a.MessageGroupID=b.MessageGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName=c.GroupName --这里如果有自建文件夹将转不过来 --删除消息Group
delete Message_Group where UserID=@UserID1
--转黑名单
update a
set FriendGroupID=c.FriendGroupID
from Friends a,FriendGroup b,FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='黑名单'
and c.GroupName='黑名单'
and not exists ( --过滤重复的黑名单
select 1 from Friends x,FriendGroup y where y.GroupName='黑名单' and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID
) --转好友
update a
set FriendGroupID=c.FriendGroupID
from Friends a,FriendGroup b,FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='我的好友'
and c.GroupName='我的好友'
and not exists ( --过滤重复的好友
select 1 from Friends x,FriendGroup y where y.GroupName='我的好友' and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID
)
--删除在黑名单中的好友
delete a
from Friends a,FriendGroup b,FriendGroup c,Friends d
where a.FriendGroupID=b.FriendGroupID
and c.FriendGroupID=d.FriendGroupID
and b.GroupName='我的好友'
and c.GroupName='黑名单'
and a.FriendUserID=d.FriendUserID
and b.UserID=@UserID4
and c.UserID=@UserID4 --删除好友Group
delete FriendGroup where UserID=@UserID1
--修改在其他用户好友中的纪录(你没提出这个要求,我想是需要改的)
update Friends set FriendUserID=@UserId4 where FriendUserID=@UserID1
delete statistics_User where UserID=@UserID1 delete User_Info where UserID=@UserID1
end--第二个用户
set @UserID1=null
select @UserID1=UserID from User_Info where UserName=@txtbox2if @UserID1 is not null and @UserID1<>@UserID4 --不相等才修改
begin
update a set TotalPosts=a.TotalPosts+b.TotalPosts
from statistics_User a,statistics_User b
where a.UserID=@UserID4 and b.UserID=@UserID1 --转消息
update a
set MessageGroupID=c.MessageGroupID
from Message a,Message_Group b,Message_Group c
where a.MessageGroupID=b.MessageGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName=c.GroupName --这里如果有自建文件夹将转不过来 --删除消息Group
delete Message_Group where UserID=@UserID1
--转黑名单
update a
set FriendGroupID=c.FriendGroupID
from Friends a,FriendGroup b,FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='黑名单'
and c.GroupName='黑名单'
and not exists ( --过滤重复的黑名单
select 1 from Friends x,FriendGroup y where y.GroupName='黑名单' and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID
) --转好友
update a
set FriendGroupID=c.FriendGroupID
from Friends a,FriendGroup b,FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='我的好友'
and c.GroupName='我的好友'
and not exists ( --过滤重复的好友
select 1 from Friends x,FriendGroup y where y.GroupName='我的好友' and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID
)
--删除在黑名单中的好友
delete a
from Friends a,FriendGroup b,FriendGroup c,Friends d
where a.FriendGroupID=b.FriendGroupID
and c.FriendGroupID=d.FriendGroupID
and b.GroupName='我的好友'
and c.GroupName='黑名单'
and a.FriendUserID=d.FriendUserID
and b.UserID=@UserID4
and c.UserID=@UserID4 --删除好友Group
delete FriendGroup where UserID=@UserID1
--修改在其他用户好友中的纪录(你没提出这个要求,我想是需要改的)
update Friends set FriendUserID=@UserId4 where FriendUserID=@UserID1 delete statistics_User where UserID=@UserID1 delete User_Info where UserID=@UserID1
end--第三个用户
set @UserID1=null
select @UserID1=UserID from User_Info where UserName=@txtbox3if @UserID1 is not null and @UserID1<>@UserID4 --不相等才修改
begin
update a set TotalPosts=a.TotalPosts+b.TotalPosts
from statistics_User a,statistics_User b
where a.UserID=@UserID4 and b.UserID=@UserID1 --转消息
update a
set MessageGroupID=c.MessageGroupID
from Message a,Message_Group b,Message_Group c
where a.MessageGroupID=b.MessageGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName=c.GroupName --这里如果有自建文件夹将转不过来 --删除消息Group
delete Message_Group where UserID=@UserID1
--转黑名单
update a
set FriendGroupID=c.FriendGroupID
from Friends a,FriendGroup b,FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='黑名单'
and c.GroupName='黑名单'
and not exists ( --过滤重复的黑名单
select 1 from Friends x,FriendGroup y where y.GroupName='黑名单' and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID
) --转好友
update a
set FriendGroupID=c.FriendGroupID
from Friends a,FriendGroup b,FriendGroup c
where a.FriendGroupID=b.FriendGroupID
and b.UserID=@UserID1 and c.UserID=@UserID4
and b.GroupName='我的好友'
and c.GroupName='我的好友'
and not exists ( --过滤重复的好友
select 1 from Friends x,FriendGroup y where y.GroupName='我的好友' and y.UserID=c.UserID
and y.FriendGroupID=x.FriendGroupID and x.FriendUserID=a.FriendUserID
)
--删除在黑名单中的好友
delete a
from Friends a,FriendGroup b,FriendGroup c,Friends d
where a.FriendGroupID=b.FriendGroupID
and c.FriendGroupID=d.FriendGroupID
and b.GroupName='我的好友'
and c.GroupName='黑名单'
and a.FriendUserID=d.FriendUserID
and b.UserID=@UserID4
and c.UserID=@UserID4 --删除好友Group
delete FriendGroup where UserID=@UserID1
--修改在其他用户好友中的纪录(你没提出这个要求,我想是需要改的)
update Friends set FriendUserID=@UserId4 where FriendUserID=@UserID1
delete statistics_User where UserID=@UserID1 delete User_Info where UserID=@UserID1
endgo
我也不是很懂SQL
我去看看先
先谢谢你了