mysql - 如何通过几个条件来计算测距？

Question

我有 2 个表:

activity
user_id login_time
1       2018-04-15
2       2018-04-18
3       2018-04-19
3       2018-04-20
1       2018-04-20
2       2018-04-20
4       2018-04-20

--

payments
user_id     amount
  1            10
  1            30
  2            100
  3            35
  4            0

我正在寻找 login_time = 20.04.2018 的用户数量。

2018 年 4 月 20 日的预期结果:

total_amount  number of users
0-10               1
10-20              0
30-50              2
50-and more        1

请帮忙

Answer 1

when 和 inner join 的用例

select case when amount<=10 then '0-10' when amount>10 and amount<20 then '10-20'
when amount>=30 and amount<50 then '30-50'
when amount>=50 then '50-and more' end as total_amount,count(payments.user_id)
from payments inner join activity on payments.user_id=activity.user_id
where login_time='2018-04-20'
group by case when amount<=10 then '0-10' when amount>10 and amount<20 then '10-20'
when amount>=30 and amount<50 then '30-50'
when amount>=50 then '50-and more' end