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

select
    id, name, grade_num
from user
right join
    (select 
        user_id,
        sum(grade_c) grade_num,
        rank()over(order by sum(grade_c) desc) r
    from
        (select
            user_id,
            case when type = 'add' then grade_num
            else -1*grade_num end grade_c
        from grade_info) s1
    group by user_id) s2
on s2.user_id = user.id
where r = 1
order by id;

用case when 把 add 的改成正数，把reduce的改成负数。