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

with ts as(
    select user_id, sum(grade_num) as s from grade_info
    where type = 'add'
    group by user_id
)
,tr as(
    select user_id, sum(grade_num) as r from grade_info
    where type = 'reduce'
    group by user_id

),
gr as( # 1 + none = none
    select id, name, ss.s-if(r is null, 0, r) as num from user u 
    left join ts ss on u.id = ss.user_id
    left join tr rr on u.id = rr.user_id
)

select id, name, num from gr
where num = (select max(num) from gr)
order by id asc

思路很清晰，选出加分的作为一个表，算出减分的作为一个表，然后加减分得到总分

尤其要注意，有的用户没有减分，那么减分表中是null，要 if(r is null, 0, r)来条件判断让它变成0，否则 5+none = none，得不到想要的结果