我有以下(简化的)表格

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 之后，九 [following_count = 2]
• alpha 紧随 7，7 紧随 alpha [mutual_count = 1]
• alpha 正在阻止五个 [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、(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;

最佳答案

列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

在阅读了您的评论并查看了您对查询的尝试后，我也有了一个“洞察力”，认为应该可以只用两个连接而不用子查询来获得结果。

可以通过以下方式实现与 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 子句(更新 2)中的 OR 条件可能会影响性能。这通常通过UNION 优化 来解决，这将导致与完全外部联接 类似的解决方案。

注2

带有子查询的 LEFT JOIN (Update 1) 在性能方面也不是最好的主意，因为没有索引可以用于 ON 子句。最好改用 INNER JOIN，并在应用程序(如果确实需要)中用缺失的用户(那些根本没有关系的用户)填充结果，或者将它们排除在外。

关于MySQL 根据条件统计行数，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/50776047/