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

with square as(
    select user_id,sum(if(type='add',grade_num,-1*grade_num)) as gr from grade_info w group by user_id
)  # 核心代码  统计和  加减

select user_id, us.name,sqs.gr from(
    select user_id,
        square.gr,   #cte 统计的数据
       dense_rank() over(order by gr desc ) as grs  #窗口函数排序
from square 
)as sqs
inner join user as us on us.id=sqs.user_id  #查询user 的name
where sqs.grs=1   #窗口函数排序  条件为最高