MySql 查询以检查 ID 列表是否是我的朋友?

Question

所以我有一个ids(userId(的列表，它是使用sql命令找到的，我想与我的朋友表一起检查他们是否确实是我自己的朋友，我将提供一个userid，并有一个列来表示他们是否是，如果他们是我的朋友而不是我的朋友，也会对他们进行分组。

例：

List of userids:
1
2
3

Friends table:
-------
userId           *the userid sending a friend request*
friendId         *the userid receiving the friend request*
relationshipId   *an unique id for the relationship*
initiated_by     *the userid initiating the friend request*
status           *whether or not the users are friends 'friends' or 'pending'*

示例好友表数据

我尝试创建一个子查询，该子查询将首先获取我想要的 id 列表，然后尝试将其与我的朋友表进行比较，但无法完全将其放在一起，因为朋友表不是双向的，这意味着每一行代表 2 个人之间的关系，状态为朋友或待定

Answer 1

使用CASE表达式获取一个人的好友(即从friends表中选取userid或friendid(。

从MySQL 8开始，您可以使用WITH子句来获得可读性：

with friends_of_user_1 as
(
select case when userid = 1 then friendid else userid end as userid
from friends
where 1 in (userid, friendid)
and status = 'friends'
)
, friends_of_user_2 as
(
select case when userid = 2 then friendid else userid end as userid
from friends
where 2 in (userid, friendid)
and status = 'friends'
)
select 
userid,
userid in (select userid from friends_of_user_2) as is_friend_of_user_2
from friends_of_user_1;

没有WITH也一样：

select 
userid,
userid in 
(
select case when userid = 2 then friendid else userid end as userid
from friends
where 2 in (userid, friendid)
and status = 'friends'
) as is_friend_of_user_2
from 
(
select case when userid = 1 then friendid else userid end as userid
from friends
where 1 in (userid, friendid)
and status = 'friends'
) friends_of_user_1;

Answer 2

你可以让所有的朋友都是双向的，即(a，b(，(b，a(，(a，c(，(c，a(，...使用UNION查询。如果保证表中不存在 (b，a( 而存在 (a，b(，那么可以使用UNION ALL，否则使用UNION。

然后，您可以按用户聚合，并查看他们是用户 1 和/或用户 2 的好友：

with all_friends as
(
select userid as userid1, friendid as userid2 from friends status = 'friends'
union
select friendid as userid1, userid as userid2 from friends status = 'friends'
)
select
userid1,
sum(userid2 = 2) > 0 as is_friends_with_user_2
from all_friends
group by userid1
having sum(userid2 = 1) > 0 -- is friends with user 1

Answer 3

第二次使用好友的别名来获取互惠关系

SELECT A.Name,...
FROM Users A
JOIN friends FA ON A.userID = FA.userID
LEFT JOIN friends FB ON FA.friedID = FB.userID
JOIN Users B on FB.userID = B.userID
...

所以FA是A的朋友，FB是B回到A的朋友关系。