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

select u.id, u.name, A.grade_num
from (
    select user_id, sum(case when type = 'add' then grade_num else -grade_num end) as grade_num,
    dense_rank()over(order by sum(case when type = 'add' then grade_num else -grade_num end) desc) as rk
    from grade_info g
    group by user_id
) A join user u on A.user_id = u.id
where rk = 1
order by u.id