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

with t1 as (
    select user_id,
    (case when type = 'add' then grade_num
    else -grade_num end) as grade_change
    from grade_info
),
# 将每一个人的总积分计算出来
t2 as (
    select user_id, sum(grade_change) as total_grade
    from t1
    group by user_id
),
t3 as (
    select user.id, user.name, t2.total_grade as grade_num
    from t2
    left join user
    on user.id = t2.user_id
    order by grade_num desc
)
select t3.*
from t3
where grade_num = (
    select grade_num
    from t3
    limit 1
)
order by id;