时间戳

首页 > 解决方案 > 如何确定过去 30 天内每位用户的邀请数？

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

问题描述

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

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;

泰

标签： mysqlmariadb

解决方案

问题是你LEFT JOIN'd在 WHERE 子句中限制你的表，有效地将它变成一个 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子句会导致在连接发生之前而不是之后过滤这些记录。

我还主张在您的子句中包含您的first_nameandlast_name字段，GROUP BY因为如果您的子句中不存在非聚合字段，那么所有其他 RDBMS 都会出错GROUP BY。我不确定较新版本的 MariaDB 是否会引发错误，但较新版本的 MySQL 肯定会（但早于 5.7 的版本会很乐意按原样运行）：

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

推荐阅读