题解 | 统计用户获得积分

select
    t.user_id,
    cast(sum(t.point) as decimal(3,0)) as point
from

(select
user_id,
case-----准确理解“每满10分钟获得1积分”的含义
    when timestampdiff(minute,visit_time,leave_time)%10=0
        then timestampdiff(minute,visit_time,leave_time)/10
    else substr(round(timestampdiff(minute,visit_time,leave_time)/10,1),1,1)
    end as point
from visit_tb
group by user_id,visit_time,leave_time) t
group by t.user_id
order by point desc;