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

SELECT
user_id,
name,
grade_num
FROM(
select 
user_id,
name,
SUM(case when type = 'add' then grade_num
     when type = 'reduce' THEN -1*grade_num 
END) AS grade_num,
rank() over(order by SUM(case when type = 'add' then grade_num
     when type = 'reduce' THEN -1*grade_num 
END) desc) AS T
from 
grade_info u1
LEFT JOIN user U ON U.id = u1.user_id
GROUP BY user_id,name
) AS GH 
WHERE GH.T = 1