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

select d.user_id,user.name,d.new
from (
    select c.user_id,c.new
    from (
        select *,rank() over(order by b.new desc) as 'rk'
        from (
            select a.user_id,sum(a.new) as 'new'
            from (
                select *,
                    case when type = 'reduce' 
                    then -grade_num 
                    else grade_num 
                    end as 'new'
                from grade_info
                )a
            group by a.user_id)b)c
    where c.rk = 1
    order by c.user_id)d
left join user
on d.user_id = user.id