php - LEFT JOIN 过滤最近 30 天加入的推荐人，而不是最近 30 天加入的推荐人

Question

我有两张 table

表用户

USER_ID------USERNAME------------REGISTERTIME  
1-----------------admin----------1432985224    
2-----------------test ----------1433985224
3-----------------test2----------1332985224     

推荐表

REFERRER_ID----------REFERRAL_ID 
1-----------------------------2
1-----------------------------3

如何用mysql显示这个:

user admin have 2 referrals in last 30 days
user test  have 0 referrals in last 30 days
user test2 have 0 referrals in last 30 days

尝试过:

SELECT
    users.username,
    COUNT(referrals.user_id) AS referrals
FROM
    users
LEFT JOIN
    referrals ON
    referrals.referral_id = users.user_id
WHERE users.registertime >= UNIX_TIMESTAMP( '2015-10-15' ) AND registertime < UNIX_TIMESTAMP( '2015-10-18' )
GROUP BY
    users.user_id

它显示的是

仅限最近 30 天加入并有推荐的用户但我需要它来显示过去 30 天内有新推荐的所有用户

错误:(这只显示最近 30 天内加入并有推荐的用户)

新用户 1 有 12 个推荐
新用户 2 有 3 个推荐人
新用户 3 有 2 个推荐

正确:(这显示了在过去 30 天内有推荐加入的所有用户)

老用户 1 有 52 个推荐
新用户 3 有 28 个推荐人
老用户 33 有 10 个推荐人

我希望我解释得很好

Answer 1

您需要引入有关推荐人的信息并将其用于过滤

SELECT u.username, COUNT(*) AS referrals
FROM users u JOIN
     referrals r
     ON r.referral_id = u.user_id JOIN
     users ur
     ON r.referrer_id = ur.user_id AND
        ur.registertime >= UNIX_TIMESTAMP( '2015-10-15' ) AND
        ur.registertime < UNIX_TIMESTAMP( '2015-10-18' )
GROUP BY u.username;

我认为您不需要为此使用LEFT JOIN。您的示例中没有一个计数为 0。如果您也需要它们，则只需更改查询中的连接即可。

您还应该养成在 GROUP BY 中使用与在 SELECT 中使用的列相同的习惯。这是一个可以防止将来出错的好习惯。