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

with t1 as(
select user_id, 
sum(case when type = 'add' then grade_num
else -1*grade_num end) as sum_grade
from grade_info
group by user_id
),
t2 as(
select user_id, sum_grade,
rank()over(order by sum_grade desc) as rank1
from t1
)
select a.id, a.name, t2.sum_grade as grade_num
from user as a
join t2 on a.id = t2.user_id and t2.rank1 = 1

用窗口函数筛选