mysql - 如何确定过去 30 天内每个用户的邀请数量？

Question

我的数据库有两个与此问题相关的表:

users: id, first_name, last_name, created_at
invitations: id, user_id

我想要输出的是过去 30 天内创建的所有用户的列表以及他们发出的邀请数量。查询所需的输出:

user.id | total_invitations
31 | 2
32 | 0
33 | 12
34 | 1
35 | 1
.....

这是我正在进行的查询，我在这里做错了什么？

SELECT u.id,
             u.first_name,
           u.last_name,
             u.invitation_approved_at,
       COUNT(i.id) AS Total
FROM users u 
LEFT JOIN invitations i
     ON u.id = i.user_id
WHERE 
    i.type = 'email'
        AND i.revoked_at IS NULL
        AND u.invitation_approved_at >= curdate() - INTERVAL 30 DAY
    AND u.invitation_approved_at < curdate() - INTERVAL -7 DAY
GROUP BY u.id
ORDER BY u.invitation_approved_at;

我

Answer 1

问题是您在 WHERE 子句中限制了 LEFT JOIN'd 表，从而有效地将其转换为 INNER JOIN。相反:

SELECT u.id,
             u.first_name,
           u.last_name,
             u.invitation_approved_at,
       COUNT(i.id) AS Total
FROM users u 
LEFT JOIN invitations i
     ON u.id = i.user_id
       AND i.type = 'email'
        AND i.revoked_at IS NULL
WHERE  u.invitation_approved_at >= curdate() - INTERVAL 30 DAY
    AND u.invitation_approved_at < curdate() - INTERVAL -7 DAY
GROUP BY u.id
ORDER BY u.invitation_approved_at;

将这两个条件移至 ON 子句会导致在连接发生之前而不是之后过滤这些记录。

我还建议在 GROUP BY 中包含 first_name 和 last_name 字段，因为如果非聚合，所有其他 RDBMS 都会出错字段不存在于您的 GROUP BY 子句中。我不确定较新版本的 MariaDB 是否会抛出错误，但较新版本的 MySQL 肯定会抛出错误(5.7 之前的版本很乐意按原样运行):

 GROUP BY u.id, u.first_name, u.last_name