MySQL根据条件对行进行计数
MySQL根据条件对行进行计数
提问于 2018-06-09 23:56:17
我有以下(简化的)表
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，
- α跟随7，7跟随αmutual_count =1，
- α阻挡5 blocking_count = 1
gamma的关系是：
- alpha遵循gamma followed_count = 1
我需要在输出中捕获上面的关系：
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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50776047
复制相关文章
相似问题
腾讯云开发者
