mysql - 如何将一个表中的多个条目连接到 MySQL 中的单个关系状态？

Question

我有一个表 Follow，它只保存 UserID 跟在 TargetID 之后的记录。

如果询问用户 A:

• 如果 A 或 B 都没有关注对方，则他们的状态为 0(表示不相关)，并且不包含在结果中。
• 如果用户 A 关注 B 但不是相反，则 B 的状态为 1被关注。
• 如果用户 B 关注 A 但不是相反，则 B 有状态 2 作为一个追随者。
• 如果 A 跟随 B，并且 B 跟随A、B 的状态为 3，是 friend 。

如何在单个 MySQL 查询中获取给定用户的关系状态及其所有高于状态 0 的关系？

例子:

Users:
+----+-------+
| id | Name  |
+----+-------+
|  1 | Bob   | 
|  2 | Steve |
|  3 | Scott | 
|  4 | Mary  | 
+----+-------+

Follow:
+----+--------+----------+
| id | UserID | TargetID |
+----+--------+----------+
|  1 |      1 |        2 |
|  2 |      1 |        3 |
|  3 |      2 |        1 |
|  4 |      4 |        1 |
+----+--------+----------+

Expected result for user 1:
+----------+--------+-------+
| TargetID | Status | Name  |
+----------+--------+-------+
|        2 |      3 | Steve | (friend)
|        3 |      1 | Scott | (following)
|        4 |      2 |  Mary | (follower)
+----------+--------+-------+

Answer 1

您可以使用子查询，如下所示:

-- FOR USER 1
SELECT A.id TargetID,
 SUM(IFNULL((SELECT 1 C FROM Follow B WHERE B.UserID=1 AND B.TargetID=A.id),0) +
     IFNULL((SELECT 2 C FROM Follow D WHERE A.id=D.UserID AND D.TargetID=1), 0)) Status
     , A.name
FROM (SELECT * FROM Users WHERE ID<>1) A
GROUP BY A.id, A.Name
HAVING Status>0; -- for a compact result

-- NOW GLOBALLY
SELECT A.UserID, A.id TargetID,
 SUM(IFNULL((SELECT 1 C FROM Follow B WHERE B.UserID=A.UserID AND B.TargetID=A.id),0) +
     IFNULL((SELECT 2 C FROM Follow D WHERE A.id=D.UserID AND D.TargetID=A.UserID), 0)) Status
     , A.name
FROM (SELECT E.id UserID, F.* FROM Users E JOIN Users F ON E.id<>F.id) A
GROUP BY A.UserID, A.id, A.Name
HAVING Status>0 -- for a compact result
ORDER BY A.UserID;

参见 DEMO on SQL Fiddle