我有以下(简化的)表
users
+----+-------+
| id | name |
+----+-------+
| 1 | alpha |
| 3 | gamma |
| 5 | five |
| 7 | seven |
| 9 | nine |
+----+-------+
user_relationships
+--------------+----------------+----------------------+
| from_user_id | target_user_id | relationship_type_id |
+--------------+----------------+----------------------+
| 1 | 3 | 1 |
| 1 | 5 | -1 |
| 1 | 7 | 1 |
| 1 | 9 | 1 |
| 7 | 1 | 1 |
+--------------+----------------+----------------------+
relationship_type_id =1代表“跟随”
relationship_type_id = -1表示“阻塞”
alpha的结果关系为:
9 following_count =2,
gamma的关系是:
我需要在输出中捕获上面的关系:
Output
+----+-------+-----------------+----------------+--------------+----------------+
| id | name | following_count | followed_count | mutual_count | blocking_count |
+----+-------+-----------------+----------------+--------------+----------------+
| 1 | alpha | 2 | 0 | 1 | 1 |
| 3 | gamma | 0 | 1 | 0 | 0 |
| 5 | five | 0 | 0 | 0 | 0 |
| 7 | seven | 0 | 0 | 1 | 0 |
| 9 | nine | 0 | 1 | 0 | 0 |
+----+-------+-----------------+----------------+--------------+----------------+
我已经花了几个小时尝试组合GROUP BY,COUNT,HAVING,DISTINCT,SUM,(SUM in SELECT)等,但就是不能正常工作。
需要帮助或指导的请。我很乐意进一步尝试。
下面是基本的MySQL查询(没有我搞砸的实验)
select
u.id,
u.name,
r1.from_user_id, r1.target_user_id, r1.relationship_type_id,
r2.from_user_id, r2.target_user_id, r2.relationship_type_id,
r3.from_user_id, r3.target_user_id, r3.relationship_type_id
from users u
join user_relationships r1
on u.id = r1.from_user_id
join user_relationships r2
on u.id = r2.target_user_id
join user_relationships r3
on u.id = r3.from_user_id or u.id = r3.target_user_id;
https://stackoverflow.com/questions/50776047
复制相似问题