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

方法一: 先对分数进行累计聚合,求出最大的累加的分数来,之后利用子查询将最大的分数作为条件,用户的分数等于最大的分数将其用户输出.

select u.id,u.name,a.grade_sum
from
(select user_id,sum(grade_num) as grade_sum
from grade_info
group by user_id
having grade_sum=(
select sum(grade_num) as grade_sum
    from grade_info
    group by user_id
    order by grade_sum desc
    limit 1
))a
join user u
on a.user_id=u.id
order by u.id;

方法二: 利用窗口函数 先将每个用户的分数进行累计聚合, 然后利用rank()函数将之前聚合的分数进行排名,最后在条件里面只限定排名第一的用户输出 窗口函数简单明了,但一定要注意限定条件.

select u.id,u.name,a.grade
from
(SELECT user_id,g.grade,rank() over(order by g.grade desc) as t_rank
from 
(SELECT user_id,sum(grade_num) over(partition by user_id) as grade
from grade_info)g
group by user_id)a
join user u
on a.user_id=u.id
where a.t_rank=1
order by u.id;