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

SELECT id,NAME,grade
FROM
(SELECT id
      ,NAME
      ,grade
      ,RANK() OVER(ORDER BY grade DESC) AS rk
FROM user AS a LEFT JOIN
(SELECT DISTINCT user_id
      ,SUM(IF(TYPE = 'add',1,-1)*grade_num) OVER (PARTITION BY user_id)AS grade
FROM grade_info
) AS b
ON a.id = b.user_id
) AS t
WHERE rk=1