I have 2 tables

TABLE USERS

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

TABLE REFERRALS

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

how to show with mysql this:

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

Tried:

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

What it shows is

only users that joined last 30 days and have referrals but i need it to show all users that HAVE NEW referrals in last 30 days

Wrong:(This shows only users that have joined in last 30 days and have referrals)

new user1 have 12 referrals new user2 have 3 referrals new user3 have 2 referrals

Correct: (this shows all users that have referrals that joined in last 30 days )

Old user1 have 52 referrals New user3 have 28 referrals Old user33 have 10 referrals

I hope i explained well

ros的joint_state_publisher可以控制urdf但是为什么监听不了joint_state_publisher的消息呢

只会git clone的程序员

1个回复