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

select
    t.user_id,
    t.name,
    t.grade_num
from
    (
        select
            t1.user_id,
            t2.name,
            t1.grade_num,
            rank() over (
                order by
                    t1.grade_num desc
            ) rk
        from
            (
                select
                    user_id,
                    sum(
                        case
                            when type = 'add' then grade_num
                            when type = 'reduce' then - grade_num
                        end
                    ) grade_num
                from
                    grade_info
                group by
                    user_id
            ) t1
            left join user t2 on t1.user_id = t2.id
    ) t
where
    rk = 1