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

/*
select info.user_id, u.name, sum(case when info.type = 'add' then info.grade_num * 1
           when info.type = 'reduce' then info.grade_num * (-1)
           end) as grade_sum
FROM grade_info as info
left JOIN user as u
ON u.id = info.user_id
GROUP BY user_id
*/
SELECT id, name, grade_sum
FROM (select info.user_id as id, u.name as name, sum(case when info.type = 'add' then info.grade_num * 1
           when info.type = 'reduce' then info.grade_num * (-1)
           end) as grade_sum, 
           DENSE_RANK() over(order BY sum(case when info.type = 'add' then info.grade_num * 1
           when info.type = 'reduce' then info.grade_num * (-1)
           end) DESC) as r
FROM grade_info as info
left JOIN user as u
ON u.id = info.user_id
GROUP BY user_id) as t
where r = 1;