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

select id,name,grade_num from
(select id,name,grade_num,
dense_rank() over(order by grade_num desc) dk
from
(select id,name,sum(gnum) as grade_num  from
(SELECT
t1.id,
t1.name,
case when
t2.type = 'add' then grade_num else grade_num * (-1) end as gnum
FROM user t1
join grade_info t2
on t1.id = t2.user_id) t1 group by id,name
order by id asc) t2) t3 where dk = 1;