我有以下（简化）表格
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关系是：
alpha跟随gamma，9 [following_count = 2] alpha跟随七个，七个跟随alpha [mutual_count = 1] alpha阻止5 [blocking_count = 1]伽玛的关系是：
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（SELECT中的SUM）等组合了几个小时，但是无法让它工作。
需要帮助或指导。 我很乐意进一步尝试。
下面的基本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;I have the following (simplified) tables
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 stands for "following"
relationship_type_id = -1 is for "blocking"
The resulting relationships for alpha are:
alpha is following gamma, nine [following_count = 2] alpha is following seven and seven is following alpha [mutual_count = 1] alpha is blocking five [blocking_count = 1]The relationships for gamma are:
alpha is following gamma [followed_count = 1]And I need the above relationships to be captured in the output:
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 | +----+-------+-----------------+----------------+--------------+----------------+I have been grappling for a few hours now with combinations of GROUP BY, COUNT, HAVING, DISTINCT, SUM, (SUM in SELECT) etc., but can't get it to work.
Need help or guidance please. I'm happy to try out further.
Basic MySQL query below (without my messed up experiments)
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;最满意答案
可以使用条件聚合实现mutual_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这样，我们在单行中的两个方向上都有关系，因此不需要follow_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
在阅读了您的评论并查看您对查询的尝试之后，我也有了“洞察力”，并认为应该可以仅使用两个连接而不是子查询来获得结果。
与FULL OUTER JOIN类似的结果可以通过以下方式实现：
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子句（ Update 2 ）中的OR条件可能会损害性能。 这通常通过UNION优化来解决，这将导致与完全外部联接类似的解决方案。
笔记2
带有子查询（ Update 1 ）的LEFT JOIN也不是关于性能的最佳选择，因为没有索引可用于ON子句。 最好使用INNER JOIN代替，并在应用程序中填充缺少用户（根本没有关系）的结果（如果确实需要）或者只是将它们排除在外。
Columns following_count, mutual_count and blocking_count can be achieved with conditional aggregation. For followed_count you can write a subquery.
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;Demo: http://rextester.com/WJED13044
Update 1
Another way is first to generate a full outer join, in order to get relations in both directions in a single row. That would be something like
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_idBut since MySQL doesn't support full outer joins we will need something like this:
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;This would return
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 │ 1This way we have the relation in both directions in a single row, and thus don't need a subquery for the followed_count column, and can use conditional aggregation instead.
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;Demo: http://rextester.com/IFGLT77163
This is also more flexible, because we now can easily add a blocked_count column with
, coalesce(sum(r2.relationship_type_id = -1), 0) as blocked_countIf you use MySQL 8 or MariaDB 10.2 this can be written a bit nicer using 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.idDemo: https://www.db-fiddle.com/f/nEDXXkrLEj9F4dKfipzN9Q/0
Update 2
After reading your comment and looking at what you have tried with your query, I had an "insight" too, and thought that it should be possible to get the result with only two joins and no subqueries.
A similar result to the FULL OUTER JOIN can be achieved with:
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)And then we only need to add the GROUP BY clause and perform our conditional aggregations as we did in other queries:
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;Demo: http://rextester.com/UAS51627
Note 1
An OR condition in the ON clause (Update 2) can hurt the performance. That is usually solved with a UNION optimization, which would lead to a similar solution as with the full outer join.
Note 2
A LEFT JOIN with a subquery (Update 1) is also not the best idea regarding performance, because no index can be used for the ON clause. It might be better to use an INNER JOIN instead, and fill the result with missing users (those who has nor relation at all) in application (if really needed) or just leave them out.
更多推荐
发布评论