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

#①先计算每个用户id总增加积分
select user_id,sum(grade_num)
from grade_info
group by user_id
#②按照总积分排序
select rank() over(order by a.num desc)
from (
    select user_id,sum(grade_num) as 'num'
    from grade_info
    group by user_id
    )a
#③查找总积分最多的用户id
select b.user_id,b.num
from(
    select *,rank() over(order by a.num desc) as 'rk'
    from (
        select user_id,sum(grade_num) as 'num'
        from grade_info
        group by user_id)a
    )b
where b.rk=1
order by b.user_id
#④连接name表查找id对应名称
select c.user_id,d.name,c.num
from (
    select b.user_id,b.num
    from(
        select *,rank() over(order by a.num desc) as 'rk'
        from (
            select user_id,sum(grade_num) as 'num'
            from grade_info
            group by user_id)a
        )b
        where b.rk=1
        order by b.user_id
    )c
left join user d
on c.user_id = d.id