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

①先计算每个人总积分
②窗口函数对总积分排序，取排名为1的用户id及对应总积分
③左连接姓名表查询用户姓名
select name,grade_num
from(
    select a.user_id,a.grade_num,rank() over(order by a.grade_num desc) as 'rank'
    from (
        select user_id,sum(grade_num) as 'grade_num' from grade_info group by user_id
    )a)b
    left join user c
    on b.user_id = c.id 
    where b.rank = 1
# 报错order by 后面不可使用聚合函数，应先子查询计算好再取字段
# select rank() over(partition by user_id order by sum(grade_num) desc) as 'rank'
# from grade_info
# # 报错①
# select user_id,max(grade_num) 
# from grade_info
# # 报错②
# select user_id,sum(grade_num) 
# from grade_info
# group by user_id
# # 报错③
# select user_id,max(sum(grade_num))
# from grade_info
# group by user_id