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

我人傻了

select t.id, t.name, t.grade_sum
from(
select total.user_id as id, u.name, total.grade_sum, dense_rank() over (order by grade_sum desc) as ranking
from (
select add_score.user_id,case
 when ( reduce_score.r is not null ) then add_score.a - reduce_score.r
 when(reduce_score.r is null) then add_score.a
end as  grade_sum
from
(select user_id, sum(grade_num) as a
from grade_info 
where type = 'add'
group by user_id) add_score
left join
(select user_id, sum(grade_num) as r
from grade_info 
where type = 'reduce'
group by user_id) reduce_score
on add_score.user_id = reduce_score.user_id) total
join user u
on  total.user_id = u.id) t
where t.ranking = 1