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

select b.user_id,b.name,b.grade_num from
(select a.user_id,a.name,a.grade_num,rank()over(order by a.grade_num desc) ranking from
(select user_id,user.name,sum(case when type='add' then grade_num else (grade_num)*(-1) end) grade_num
from grade_info join user on user.id=grade_info.user_id
group by user_id,user.name) a
group by a.user_id,a.name) b
where b.ranking=1
order by b.user_id