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

select user_id
       ,name
       ,grade_sum
from (
    select t1.user_id
           ,t1.name
           ,sum(t1.score) as grade_sum
           ,DENSE_RANK() over (order by sum(t1.score) desc ) as ranking
    from (select     a.user_id
                     ,b.name
                     ,case when a.type = "add" then 1*a.grade_num
                           when a.type = "reduce" then (-1)*a.grade_num
                           end as score 
          from grade_info as a
          join user as b
          on a.user_id = b.id) t1
    group by t1.user_id
             ,t1.name)  t2
where ranking = 1;