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

积分有增有减，可以加个if或case when 进行判断，并赋予负值；求出最高的积分可以找出最大积分进行对比找出，也可以利用开窗函数。

with gs as
(select a.id, a.name,
sum(if(b.type = 'reduce', -b.grade_num, b.grade_num)) as sum_grade
from user a left join grade_info b 
on a.id = b.user_id
group by a.id)
select gs.id as id, gs.name as name, gs.sum_grade as grade_sum from gs 
where sum_grade = (select max(sum_grade) from gs)
order by id;