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

在前面的基础上，需要变化的点是type为‘reduce’就是负数，所以sum与case when 联合使用，即sum(case when type='reduce' then -g.grade_num else g.grade_num end)as t_num，其他和前两题没什么区别

select
    id,
    name,
    b.t_num as grade_num
from
    (
        select
            user_id,
            t_num,
            rank() over (
                order by
                    t_num desc
            ) as r
        from
            (
                select
                    user_id,
                    sum(case when type='reduce' then -g.grade_num else g.grade_num end)as t_num
                from
                    grade_info as g
                group by
                    user_id
            ) as a
    ) as b
    join user as u on u.id = b.user_id
where
    b.r = 1
    order by id