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

select 
    a1.id,
    a1.name,
    a1.grade_num
from (
    select
        t1.id,
        t1.name,
        ifnull(t2.grade_num,0) + ifnull(t3.grade_num,0) grade_num,
        -- 给所有用户的分数进行排名打标，并列分数排名相同
        rank() over(order by ifnull(t2.grade_num,0) + ifnull(t3.grade_num,0) desc) rn
    from user t1
    left join (
        -- 求每个用户总加分数
        select
            user_id,
            sum(grade_num) grade_num
        from grade_info
        where type = 'add'
        -- 可能存在多条加分记录
        group by user_id
    ) t2
    on t1.id = t2.user_id
    left join(
        -- 求每个用户总减分数
        select
            user_id,
            sum(grade_num) * (-1) grade_num
        from grade_info
        where type = 'reduce'
        -- 可能存在多条减分记录
        group by user_id
    ) t3
    on t2.user_id = t3.user_id
) a1
-- 取并列第一
where a1.rn = 1
order by a1.id;