在MySQL中如何根据条件对行进行计数?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (218)

有以下表格:

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 |
+--------------+----------------+----------------------+

我需要在输出中捕获上述关系:

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 |
+----+-------+-----------------+----------------+--------------+----------------+

下面是我的代码:

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;
提问于
用户回答回答于

Columns 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
        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;

或者这样:

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

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

使用聚合:

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;

, coalesce(sum(r2.relationship_type_id = -1), 0) as blocked_count

如果使用MySQL 8或MariaDB10.2,则可以使用以下方法:

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
用户回答回答于

尝试使用子查询解决方案;

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

扫码关注云+社区

领取腾讯云代金券