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

当 type='add'时，直接进行 sum() 求和，可是当 type='reduce' 时，需要减掉，相当于乘以 -1，
上述场景，可以通过 case when ... then ... else ... end 实现。

select user_id, name, gn
from (
    select user_id, gn,
        dense_rank() over(order by gn desc) as rn
    from (
        select 
            user_id,
            sum(case when type='add' then grade_num else grade_num * (-1) end) as gn
        from grade_info
        group by user_id
    ) a
) b
left join user u 
on b.user_id = u.id
where rn = 1
;