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

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

二刷 简单