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

# grade_info 需要将type=reduce的grade_num转化为负数
with 
grade_info_real as
(select
*
,case 
    when type='add' then grade_num
    when type='reduce' then -grade_num
end as real_num
from grade_info
)
,total_grade as
(select
*
,sum(g1.real_num)over(partition by g1.user_id) as sum1
from grade_info_real g1
)

select
distinct
t.user_id as id
,u.name as name 
,t.sum1 as grade_num
from total_grade t left join user u
on t.user_id= u.id
where t.sum1 in (select max(sum1) from total_grade)