题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
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;
