关注、互粉
关系型数据库虽然存储⽤户的关系很简单，但是要求出⽤户关注被关注和互粉这种这块特别的⿇烦，特别是⽤户A的粉丝与⽤户B的粉丝之
间的关系，或者⽤户A的关注者与⽤户A⾃⼰的粉丝直接的关系等。
下⾯是⼀个要求：
功能：A关注B B关注A A取消关注B B取消关注A
求出关系：A的粉丝列表 A的关注列表 A的互粉列表
⽅案1
测试准备
1.创建两个表：CREATE TABLE [dbo].[FollowTest_User](
[Id] [int] IDENTITY(1,1) NOT NULL,
金陵十二钗[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_FollowTest_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLO
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[FollowTest_UserFollow](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FollowUser] [int] NOT NULL,
[BeFollowedUser] [int] NOT NULL,经典老歌歌曲大全
CONSTRAINT [PK_FollowTest_UserFollow] PRIMARY KEY CLUSTERED
演员李泌(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLO
) ON [PRIMARY]
GO
2.插⼊⼀些数据做测试：insert into FollowTest_User(Name) values('A');
insert into FollowTest_User(Name) values('B');
insert into FollowTest_User(Name) values('C');
insert into FollowTest_User(Name) values('D');
insert into FollowTest_User(Name) values('E');
insert into FollowTest_User(Name) values('F');
insert into FollowTest_User(Name) values('G');
insert into FollowTest_User(Name) values('H');
insert into FollowTest_User(Name) values('I');
insert into FollowTest_User(Name) values('J');
insert into FollowTest_User(Name) values('K');
insert into FollowTest_User(Name) values('L');
insert into FollowTest_User(Name) values('M');
insert into FollowTest_User(Name) values('N');
insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(2,1);
insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(6,1);
insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(9,1);
insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(11,1);
insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(1,6);
insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(1,10);
insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(1,2);
表数据如下：
功能实现
A关注G：insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(1,7); G关注A：insert into FollowTest_UserFollow(FollowUser,BeFollowedUser) values(7,1);
A取消关注G：delete FollowTest_UserFollow where FollowUser=1 and BeFollowedUser=7;
G取消关注A：delete FollowTest_UserFollow where FollowUser=7 and BeFollowedUser=1;
求出关系--A的粉丝列表
select uf.FollowUser
from FollowTest_UserFollow uf
left join FollowTest_User u on u.Id=uf.BeFollowedUser
where u.Name='A' and uf.BeFollowedUser=u.Id;
--A的关注列表
select uf.BeFollowedUser
王祖蓝的爸爸王严化from FollowTest_UserFollow uf
left join FollowTest_User u on u.Id=uf.FollowUser
where u.Name='A' and uf.FollowUser=u.Id;
--A的互粉列表
select uf2.BeFollowedUser as FollowAndBeFollowed
from FollowTest_UserFollow uf2
left join FollowTest_User u2 on u2.Id=uf2.FollowUser
where u2.Name='A' and uf2.FollowUser=u2.Id
and uf2.BeFollowedUser in(
select uf1.FollowUser
from FollowTest_UserFollow uf1
left join FollowTest_User u1 on u1.Id=uf1.BeFollowedUser
where u1.Name='A' and uf1.BeFollowedUser=u1.Id);
⽅案2
新增⼀个表FollowTest_MutualFollow表⽰互粉表，为了后⾯的判断简单点，约定User1>User2。CREATE TABLE [dbo].
[FollowTest_MutualFollow](
[Id] [int] IDENTITY(1,1) NOT NULL,
[User1] [int] NOT NULL,
[User2] [int] NOT NULL,
CONSTRAINT [PK_FollowTest_Mutual] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLO
) ON [PRIMARY]
小白兔的生活习性GO
为了测试，往FollowTest_MutualFollow插⼊⼏条记录：insert into FollowTest_MutualFollow(User1,User2) values(1,6);
insert into FollowTest_MutualFollow(User1,User2) values(1,2);
insert into FollowTest_MutualFollow(User1,User2) values(3,6);
insert into FollowTest_MutualFollow(User1,User2) values(6,9);
1.当有关注的时候，在FollowTest_UserFollow判断是否是相互关注，若是则往FollowTest_MutualFollow插⼊⼀条记录。
2.当有取消关注的时候，先在FollowTest_MutualFollow判断下是否这⼆者有互粉情况，有的话则删除这条记录，再删除FollowTest_UserFollow表⼀条记录。
冀怎么读
3.统计A的粉丝列表和A的关注列表和⽅案1⼀样。
4.统计F的互粉列表：select * from FollowTest_MutualFollow where User1=6 or User2=6。
发布评论