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

SELECT id, name, grade_sum
FROM
(SELECT user_id, 
grade_sum, 
DENSE_RANK() OVER(ORDER BY grade_sum DESC) AS 't_rank'
FROM
(SELECT user_id, 
(SUM(IF(type = 'add', grade_num, 0))-SUM(IF(type = 'reduce', grade_num, 0))) AS 'grade_sum'
FROM grade_info
GROUP BY user_id) t1) t2,
user u
WHERE t2.user_id = u.id AND t_rank = 1
ORDER BY id;