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

select 
    t.user_id
    ,r.name
    ,t.sum_grade
from
    (select 
        s.user_id
        ,s.sum_grade
        ,dense_rank() over (order by s.sum_grade  desc) as `row_number`
    from
        (select
            user_id
            ,sum(case type 
                when  "add"    then grade_num
                else  grade_num*(-1) 
                end) as sum_grade
        from 
        grade_info
        group by 
            user_id) s ) t left join user r   on t.user_id=r.id
where 
    t.`row_number`=1