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;回答 2
Stack Overflow用户
回答已采纳
发布于 2018-06-10 02:03:06
列following_count、mutual_count和blocking_count可以通过条件聚合来实现。对于followed_count，您可以编写一个子查询。
select u.id, u.name
, coalesce(sum(r.relationship_type_id = 1 and r1.relationship_type_id is null), 0) as following_count
, coalesce(sum(r.relationship_type_id = 1 and r1.relationship_type_id = 1), 0) as mutual_count
, coalesce(sum(r.relationship_type_id = -1), 0) as blocking_count
, (
select count(*)
from user_relationships r2
left join user_relationships r3
on r3.from_user_id = r2.target_user_id
and r3.target_user_id = r2.from_user_id
where r2.target_user_id = u.id
and r2.relationship_type_id = 1
and r3.from_user_id is null
) as followed_count
from users u
left join user_relationships r on r.from_user_id = u.id
left join user_relationships r1
on r1.from_user_id = r.target_user_id
and r1.target_user_id = r.from_user_id
group by u.id, u.name;演示：http://rextester.com/WJED13044
更新1
另一种方法是首先生成一个完整的外连接，以便在单行中获得两个方向的关系。那就像这样
select *
from user_relationships r1
full outer join user_relationships r2
on r2.from_user_id = r1.target_user_id
and r1.from_user_id = r2.target_user_id但是因为MySQL不支持完全的外连接，所以我们需要这样的东西：
select r.*, r1.relationship_type_id as type1, r2.relationship_type_id as type2
from (
select from_user_id uid1, target_user_id uid2 from user_relationships
union distinct
select target_user_id uid1, from_user_id uid2 from user_relationships
) r
left join user_relationships r1
on r1.from_user_id = r.uid1
and r1.target_user_id = r.uid2
left join user_relationships r2
on r2.target_user_id = r.uid1
and r2.from_user_id = r.uid2;这将返回
uid1 │ uid2 │ type1 │ type2
─────┼──────┼───────┼──────
7 │ 1 │ 1 │ 1
1 │ 7 │ 1 │ 1
1 │ 3 │ 1 │ null
1 │ 5 │ -1 │ null
1 │ 9 │ 1 │ null
3 │ 1 │ null │ 1
5 │ 1 │ null │ -1
9 │ 1 │ null │ 1这样，我们在一行中就有了两个方向的关系，因此不需要对followed_count列进行子查询，而是可以使用条件聚合。
select u.id, u.name
, coalesce(sum(r1.relationship_type_id = 1 and r2.relationship_type_id is null), 0) as following_count
, coalesce(sum(r2.relationship_type_id = 1 and r1.relationship_type_id is null), 0) as followed_count
, coalesce(sum(r1.relationship_type_id = 1 and r2.relationship_type_id = 1), 0) as mutual_count
, coalesce(sum(r1.relationship_type_id = -1), 0) as blocking_count
from users u
left join (
select from_user_id uid1, target_user_id uid2 from user_relationships
union distinct
select target_user_id uid1, from_user_id uid2 from user_relationships
) r on r.uid1 = u.id
left join user_relationships r1
on r1.from_user_id = r.uid1
and r1.target_user_id = r.uid2
left join user_relationships r2
on r2.target_user_id = r.uid1
and r2.from_user_id = r.uid2
group by u.id, u.name
order by u.id;演示：http://rextester.com/IFGLT77163
这也更加灵活，因为我们现在可以使用以下命令轻松地添加blocked_count列
, coalesce(sum(r2.relationship_type_id = -1), 0) as blocked_count如果您使用的是MySQL 8或MariaDB 10.2，则可以使用CTE编写得更好一些
with bdr as ( -- bidirectional relations
select from_user_id uid1, target_user_id uid2 from user_relationships
union distinct
select target_user_id uid1, from_user_id uid2 from user_relationships
), rfoj as ( -- relations full outer join
select uid1, uid2, r1.relationship_type_id type1, r2.relationship_type_id type2
from bdr
left join user_relationships r1
on r1.from_user_id = bdr.uid1
and r1.target_user_id = bdr.uid2
left join user_relationships r2
on r2.target_user_id = bdr.uid1
and r2.from_user_id = bdr.uid2
)
select u.id, u.name
, coalesce(sum(type1 = 1 and type2 is null), 0) as following_count
, coalesce(sum(type2 = 1 and type1 is null), 0) as followed_count
, coalesce(sum(type1 = 1 and type2 = 1), 0) as mutual_count
, coalesce(sum(type1 = -1), 0) as blocking_count
, coalesce(sum(type2 = -1), 0) as blocked_count
from users u
left join rfoj r on r.uid1 = u.id
group by u.id, u.name
order by u.id演示：https://www.db-fiddle.com/f/nEDXXkrLEj9F4dKfipzN9Q/0
更新2
在阅读了您的评论并查看了您对查询所做的尝试之后，我也有了一个“洞察力”，并认为应该可以只使用两个joins而不是子查询来获得结果。
使用以下命令可以获得与完全外连接类似的结果：
select u.*
, coalesce(r1.from_user_id, r2.target_user_id) as uid1
, coalesce(r2.from_user_id, r1.target_user_id) as uid2
, r1.relationship_type_id as type1
, r2.relationship_type_id as type2
from users u
left join user_relationships r1 on r1.from_user_id = u.id
left join user_relationships r2
on r2.target_user_id = u.id
and (r2.from_user_id = r1.target_user_id or r1.from_user_id is null)然后，我们只需要添加GROUP BY子句，并像在其他查询中一样执行条件聚合：
select u.id, u.name
, coalesce(sum(r1.relationship_type_id = 1 and r2.relationship_type_id is null), 0) as following_count
, coalesce(sum(r2.relationship_type_id = 1 and r1.relationship_type_id is null), 0) as followed_count
, coalesce(sum(r1.relationship_type_id = 1 and r2.relationship_type_id = 1), 0) as mutual_count
, coalesce(sum(r1.relationship_type_id = -1), 0) as blocking_count
from users u
left join user_relationships r1 on r1.from_user_id = u.id
left join user_relationships r2
on r2.target_user_id = u.id
and (r2.from_user_id = r1.target_user_id or r1.from_user_id is null)
group by u.id, u.name
order by u.id;演示：http://rextester.com/UAS51627
注1
ON子句(更新2)中的OR条件可能会损害性能。这通常是通过UNION优化解决的，这将导致与完全外连接类似的解决方案。
注2
包含子查询的LEFT JOIN (更新1)也不是性能方面的最佳选择，因为ON子句不能使用任何索引。使用内部连接可能会更好，并用应用程序(如果确实需要)中缺少的用户(那些根本没有关系的用户)来填充结果，或者干脆省略它们。
Stack Overflow用户
发布于 2018-06-10 00:12:15
对我来说，我会尝试使用子查询解决方案。例如：
SELECT
u.id,
u.name,
(
SELECT COUNT(ur.from_user_id)
FROM user_relationships as ur
WHERE
ur.from_user_id = u.id AND
NOT EXISTS (
SELECT 1 FROM user_relationships AS ur1
WHERE
ur1.target_user_id = u.id AND
ur1.from_user_id = ur.target_user_id
) AND
ur.relationship_type_id = 1
) AS following_count,
(
SELECT COUNT(ur.target_user_id)
FROM user_relationships AS ur
WHERE ur.target_user_id = u.id
AND ur.relationship_type = 1
) AS followed_count,
(
SELECT COUNT(ur.from_user_id)
FROM user_relationships as ur
WHERE
ur.from_user_id = u.id AND
EXISTS (
SELECT 1 FROM user_relation_ship AS ur1
WHERE
ur1.target_user_id = u.id AND
ur1.from_user_id = ur.target_user_id
) AND
ur.relationship_type_id = 1
) AS mutual_count,
(
SELECT COUNT(ur.from_user_id)
FROM user_relationships as ur
WHERE
ur.from_user_id = u.id AND
ur.relationship_type_id = -1
) AS blocked_count
FROM users AS u页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50776047
复制相关文章
相似问题