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

开窗函数处理

select
    t.id,
    t.name,
    t.grade_num
from
    (
        select
            u.id,
            u.name,
            sum(if (g.type = 'add', g.grade_num, - g.grade_num)) as grade_num,
            dense_rank() over (
                order by
                    sum(if (g.type = 'add', g.grade_num, - g.grade_num)) desc
            ) rk
        from
            user u
            left join grade_info g on u.id = g.user_id
        group by
            u.id,
            u.name
    ) t
where
    t.rk <= 1
order by
    id