“您可能认识的人”SQL查询

select u.id, u.email, u.name, u.etc
-- Get all my friends
from Friendships as f1
-- Get their friends
inner join Friendships as f2
    on f1.friend_id = f2.user_id
-- Get their friends User information
inner join Users as u
    on f2.friend_id = u.id
where f1.user_id = @userId

会是我开始的地方。

Select friend_id from friendships where user_id IN (select friendid from freindships where userid = <userid>)

考虑到用户7和9是朋友。对于用户7,此查询将为您提供用户9的朋友列表。

select * from 
(select distinct friendsOfFriends.friends_id, count(1) as mutualfriends
  from FRIENDSHIPS friendsOfFriends
 inner join FRIENDSHIPS friends ON friends.friend_id = friendsOfFriends.user_id
  where friends.user_id = @myuserid
  group by friendsOfFriends.friends_id) t
order by mutualfriends desc

您可以使用下面的查询;

查询 :

Select * from [USERS] U where id not in 
((select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=1)
union (select F.user_id as id from [FRIENDSHIPS] f where f.friend_id='" + Session["UserId"] + "' and f.Status=1)
union (select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=0)
union (select F.friend_id as id from [FRIENDSHIPS] f where f.user_id='" + Session["UserId"] + "' and f.Status=2))
and U.id !='" + Session["UserId"] + "'

注意:状态可能是“ 一 “,” 零 “,” 二 “作为默认参数。它们的含义是;

一 :已确认 零 :尚未确认 二 :拒绝

我根据这些参数编写了上面的查询代码。你的也可能是这样。

别紧张!