如何定义top-3评级？

nbysray5 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(271)

我有三张table：

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

payments

user_id
15
17
11

根据“活动”中的用户数量，我应该如何查询才能在2018年5月14日对设备进行前三名评级？
需要三列：

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;

但我无法显示付款用户

sql mysql Join Database select

来源：https://stackoverflow.com/questions/52154796/how-to-define-top-3-rating

2条答案

按热度按时间

p8h8hvxi1#

使用左联接付款

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

赞(0）回复(0）举报 2021-06-20

bqujaahr2#

我想你只是想再来一杯 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;

赞(0）回复(0）举报 2021-06-20

我来回答

如何定义top-3评级？

2条答案

相关问题

热门标签

最新问答