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

方法一：

1. 查询每个用户获得的总积分

   select user_id, sum(grade_num) as gn 
   from grade_info group by user_id 

2. 使用窗口函数 dense_rank() 按照总积分成绩降序paim

   select 
   user_id, gn,
   dense_rank() over(order by gn desc) as rn
   from (
    select user_id,
        sum(grade_num) as gn
    from grade_info
    group by user_id
    ) a
   ) b

3. 排名第一的就是积分最高的人，最终代码如下：

   select user_id, name, gn
   from (
    select user_id, gn,
        dense_rank() over(order by gn desc) as rn
    from (
        select user_id,
            sum(grade_num) as gn
        from grade_info
        group by user_id   
    ) a
   ) b 
   left join user u
   on b.user_id = u.id
   where rn = 1
   ;

   方法二：聚合函数和窗口函数结合
   下面这这种方法更简洁。

   select user_id, name, gn
   from (
    select user_id, 
        sum(grade_num) as gn,
        dense_rank() over(order by sum(grade_num) desc) as rn
    from grade_info
    group by user_id
   ) a 
   left join user u
   on a.user_id = u.id
   where rn = 1
   ;