题解 | #获得积分最多的人(三)#

select 
    t1.id  as 'id'
    ,u.name  as 'name'
    ,t1.grade as 'grade_num'
from
(
select 
    t0.user_id id
    ,t0.grade  grade
    ,dense_rank()over(order by t0.grade desc) r
from 
(
select 
   a.user_id user_id
   ,a.add-ifnull(b.red,0) grade
from

(
select 
    user_id 
    ,sum(grade_num) as 'add'
from 
    grade_info 
where 
    type='add'
group by 
    user_id 
) a
left join

(
select 
    user_id 
    ,sum(grade_num) as 'red'
from 
    grade_info 
where 
    type='reduce'
group by 
    user_id 
) b
on a.user_id=b.user_id
) t0
) t1 
left join 
    user u 
on 
    t1.id=u.id
where 
    t1.r=1
order by 
    t1.id