mysql - 在同一张表上选择左联接的多个计数

Question

我不确定这是否可以完成，我有一个用户表和一个关联的用户事件表连接到一个外键上。事件有不同的类型，例如评论等。我需要根据每种不同类型的事件数量过滤用户。

我目前的情况是这样的:

SELECT
      users.*, 
      COUNT(t1.id) AS comments, 
      COUNT(t2.id) AS likes
FROM users
     LEFT JOIN activity AS t1 ON users.id = t1.user_id
     LEFT JOIN activity AS t2 ON users.id = t2.user_id
WHERE t1.activity_type_id = 1 AND t2.activity_type_id = 2
GROUP BY users.id
HAVING comments >= 5 AND likes >= 5

这似乎很接近，但它返回的用户有 5 个赞和评论，而实际上用户有 5 个赞和 1 个评论。

明确地说，我希望此查询返回拥有 5 个或更多赞的用户以及还有 5 个或更多评论的用户。

更新:

我创建了一个 SQL Fiddle。在这种情况下，我有 3 个用户:

• 用户 1:6 条评论，8 个赞
• 用户 2:3 条评论，2 个赞
• 用户 3:5 条评论，2 个赞

我希望查询仅返回用户 1 和 3，以及他们各自的点赞和评论总数。

http://sqlfiddle.com/#!2/dcc63/4

Answer 1

您可以使用条件求和来进行计数，由于 MySQL 处理 bool 表达式的方式，表达式如 sum(case when et.name = 'comment' then 1 else 0 end)( “普通”SQL 语法)可以简化为 sum(case when et.name = 'comment')。

SELECT
  u.id, 
  sum(et.name = 'comment') AS comments, 
  sum(et.name = 'like') AS likes
FROM users AS u
LEFT JOIN engagements AS  e ON u.id = e.user_id
JOIN engagement_types AS et ON e.engagement_type_id = et.id
GROUP BY u.id 
HAVING sum(et.name = 'comment') >= 5 
    OR sum(et.name = 'like') >= 5

结果:

| ID | COMMENTS | LIKES |
|----|----------|-------|
|  1 |        6 |     8 |
|  3 |        5 |     2 |

Sample SQL Fiddle