如何定义前三名评级?

Question

我有 3 个表：

users 
id  device
11  SM-G955F
12  iPhone8,2
13  SM-G955F
14  LG-H812
15  SM-G955F
16  SM-G955F
17  iPhone8,2

阿拉伯数字。

activity
user_id login_time
11  2018-05-11
12  2018-05-11
13  2018-05-11
14  2018-05-12
14  2018-05-14
15  2018-05-14
11  2018-05-14
12  2018-05-14

3

payments
user_id
15
17
11

我应该做什么查询才能根据活动的用户数量在 14.05.2018 上对设备进行前 3 名评级？

需要三列：

device      number_of_users         number_of_users 
(from activity)         (from payments if there were)

这是我的查询：

select u.device, count(distinct u.id) as number_of_users from users u inner 
join activity a on a.user_id = u.id where a.login_time = '2018-04-18' group 
by u.device order by number_of_users DESC limit 3;

但我无法显示付款用户

Answer 1

我认为您只需要另一个join和count(distinct)：

select u.device, count(distinct u.id) as number_of_users,
count(distinct p.user_id) as number_of_payment_users
from users u inner join
activity a
on a.user_id = u.id left join
payments p
on p.user_id = u.id
where a.login_time = '2018-04-18'
group by u.device
order by number_of_users desc
limit 3;

Answer 2

使用左加入付款

select u.device, count(distinct u.id) as number_of_users,
count(p.user_id) as cntpay
from users u inner 
join activity a on a.user_id = u.id 
left join  paymets p on u.id=p.user_id
where a.login_time = '2018-05-14'
group by u.device
order by number_of_users DESC limit 3