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

select b.user_id,b.name,new_sum
from
(
    select a.user_id,a.name,sum(a.new_grade) as new_sum
    ,dense_rank()over(order by sum(a.new_grade) desc) as no_grade
    from
    (
        select user_id,u.name
        ,case when type= 'reduce' then grade_num *(-1) else grade_num*1 end as new_grade
        from grade_info  g
        left join user u
        on g.user_id = u.id
    )a
group by 1
order by 3
)b
where b.no_grade = 1