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

思路：
1\ 对所有add 和 reduce分数相加
2\ grade_add - grade_reduce， 并且用窗口函数保留最大的分数
3\ 输出 总分数 >= 最大分 的用户

SELECT
id,name,grade_sum
from(
SELECT
distinct
id,
name,
(grade_add - grade_reduce) as grade_sum,
max(grade_add - grade_reduce) over() as max_grade
from(
SELECT
t1.id,
t1.name,
sum(case when type = 'add' then grade_num else 0 end) as grade_add,
sum(case when type = 'reduce' then grade_num else 0 end) as grade_reduce
from user t1
left join grade_info t2
on t1.id = t2.user_id
group by t1.id,
t1.name
) t
) tt
where grade_sum >= max_grade