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

# 运用左连接将每一个用户的积分变化记录查找出来
with t1 as (
    select user_id as id, b.name,
    (case when a.type = 'add' then grade_num else -grade_num end) as jifen_change
    from grade_info as a
    left join user as b
    on a.user_id = b.id
),
t2 as (
    select id, name, sum(jifen_change) as grade_num
    from t1
    group by id, name
    order by id
)
select *
from t2
where grade_num = (
    select max(grade_num) as grade_num
    from t2
);