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

with t1 as (select user_id, type, sum(grade_num) as st
            from grade_info
            group by user_id, type),
t3 as (select t1.user_id, t1.st as ad ,t2.st as re
from t1
         left join t1 t2
                   on t1.user_id = t2.user_id and t1.type = 'add' and t2.type = 'reduce'
where t1.type = 'add')
select user_id,t4.name,ad-ifnull(re,0)
from t3
         left join user t4
                   on t4.id = t3.user_id
where ad-ifnull(re,0) in(select max(ad-ifnull(re,0)) from t3 )
order by user_id asc;