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

select u.id, u.name, B.grade_num
from (
    select gi.user_id, sum(case when `type` = 'add' then grade_num*(1) else grade_num*(-1) end) as grade_num,
    dense_rank()over(order by sum(case when `type` = 'add' then grade_num*(1) else grade_num*(-1) end) desc) as rk
    from grade_info gi 
    group by gi.user_id) as B
    join user u on B.user_id = u.id 
where rk = 1
order by u.id 

与官方题解相比，多考虑了type加减分的情况，而不是只考虑加分的情况。