mysql - 从一个列表中的表中获取 Follower 和 FollwedBy Users

Question

我有一个跟踪关注者的表

FollowerUserId, FollowingUserId
1                   2
2                   1
3                   1
4                   1
1                   5

我想获取给定 Id 关注的所有用户，然后是或两者。例如，对于 UserId 1，我希望结果为:(FG:关注，FD:关注，B:双向)

2,B
5,FG
3,FD
4,FD

我可以很容易的通过union得到FG和FD

Select FollowerUserId, 'FD' From Table Where FollowingUserId =1
Union
Select FollowingUserId, 'FG' From Table Where FollowerUserId =1;

通过上面我得到用户 2 作为

2,FG 
2,FD 

从上面但我真的需要 2,B 没有 UserId 2 重复。如何有效地完成这项工作？

Answer 1

您可以对基本查询使用聚合:

SELECT UserId,
       (CASE WHEN COUNT(DISTINCT which) = 1 THEN MIN(which)
             ELSE 'B'
        END)
FROM (Select FollowerUserId as UserId, 'FD' as which From Table Where FollowingUserId = 1
      Union ALL
      Select FollowingUserId, 'FG' From Table Where FollowerUserId = 1
     ) f
GROUP BY UserId;