现在的sns都有共同好友的功能 网上找了下 数据库的大概实现 --用户表 IF OBJECT_ID('[tbUser]') IS NOT NULL DROP TABLE [tbUser] GO create table tbUser( userId int identity(1000,1) primary key,--用户id password varchar(80) not null,--密码 name varchar(20) not null,--名字 info varchar(80)--个人信息 ); go --alter table tbUser alter column name varchar(20) not null --alter table tbUser add password varchar(80) not null --好友表 IF OBJECT_ID('[tbFriends]') IS NOT NULL DROP TABLE [tbFriends] GO Create table tbFriends( userId int, friendId int,--好友id friendType int--好友类型 ); go --insert into tbF go --好友类型表 IF OBJECT_ID('[tbFriendType]') IS NOT NULL DROP TABLE [tbFriendType] GO create table tbFriendType( userId int, friendType int , typeName varchar(20));--类型名字 go --添加好友表的外键userId --ALTER TABLE [dbo].tbFriends ADD CONSTRAINT [FK_userId] FOREIGN KEY(userId) --REFERENCES [dbo].tbUser (userId) --ON DELETE CASCADE GO create trigger tirg_friendType on tbUser for insert as begin insert into tbfriendType values( IDENT_CURRENT ( 'tbUser'),1,'我的好友'); insert into tbFriends values( IDENT_CURRENT ( 'tbUser'), IDENT_CURRENT ( 'tbUser'),1); end go insert into tbUser values(1,'测试名字1','psw'); insert into tbUser values(1,'测试名字2','psw'); insert into tbUser values(1,'测试名字3','psw'); insert into tbUser values(1,'测试名字4','psw'); insert into tbUser values(1,'测试名字5','psw'); insert into tbUser values(1,'测试名字6','psw'); insert into tbUser values(1,'测试名字7','psw'); insert into tbUser values(1,'测试名字8','psw'); insert into tbUser values(1,'测试名字9','psw'); insert tbFriends values(1000,1001,1); insert tbFriends values(1000,1002,1); insert tbFriends values(1000,1003,1); insert tbFriends values(1000,1004,1); insert tbFriends values(1000,1005,1); insert tbFriends values(1001,1000,1); insert tbFriends values(1001,1003,1); insert tbFriends values(1001,1006,1); insert tbFriends values(1002,1000,1); insert tbFriends values(1002,1003,1); insert tbFriends values(1002,1009,1); select * from tbUser select * from tbFriends select * from tbFriendType -->SQL查询如下: --1.新增一列,用来标识好友关系组 ALTER TABLE tbFriends ADD GroupID INT GO --2.更新新增列为userID UPDATE tbFriends SET GroupID=userId GO --3.更新组别号 DECLARE @userId INT,@friendId INT DECLARE C CURSOR FOR SELECT userId,friendId FROM tbFriends OPEN C FETCH NEXT FROM C INTO @userId,@friendId WHILE @@FETCH_STATUS = 0 BEGIN UPDATE tbFriends SET GroupID=(SELECT MIN(GroupID) FROM tbFriends WHERE friendId=@friendId) WHERE userId=@userId FETCH NEXT FROM C INTO @userId,@friendId END CLOSE C DEALLOCATE C GO --4.获取有最多共同好友的人 create procedure getTopBothNumFriends @MyUserID int,--我的ID @MyFriendID int output,--好友的id @Numbers int output --有多少个 as begin select top 1 @MyFriendID=friendid,@Numbers=COUNT(1) from tbFriends where GroupID IN( select GroupID from tbFriends where userId=@MyUserID) group by friendId order by COUNT(1) desc end go --调用查询: declare @MyFriendID int,@Numbers int exec getTopBothNumFriends 1001,@MyFriendID out,@Numbers out select @MyFriendID,@Numbers /* ----------- ----------- 1003 4 (1 行受影响) */ --5.获取和这个人有哪些共同好友 create PROCEDURE dbo.getBothFriends @MyUserID int,--我的ID @MyFriendId int,--好友的id @OurBothFriendId varchar(100) output,--共同好友的id @OurBothFriendName varchar(100) output--共同好友的名字 as begin select @OurBothFriendId = ISNULL(@OurBothFriendId+',','')+LTRIM(userId), @OurBothFriendName=ISNULL(@OurBothFriendName+',','')+ (select name from tbUser where userId=t.userId) from tbFriends t where friendId in(@MyUserID ,@MyFriendId) and userId not in(@MyUserID ,@MyFriendId) and exists( select 1 from tbFriends where friendId in(@MyUserID ,@MyFriendId) and userId=t.userId and friendId<>t.friendId) group by userId end go declare @OurBothFriendId varchar(20),@OurBothFriendName varchar(20) EXECUTE getBothFriends 1000,1003,@OurBothFriendId out,@OurBothFriendName out select @OurBothFriendId,@OurBothFriendName /* -------------------- -------------------- 1001,1002 测试名字2,测试名字3 (1 行受影响) */