题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
SELECT DISTINCT(id),NAME,grade_sum
FROM user u
LEFT JOIN
(SELECT ,RANK() OVER (ORDER BY grade_sum DESC) r
FROM
(SELECT user_id,SUM(g) OVER (PARTITION BY user_id) grade_sum
FROM
(
SELECT user_id, grade_num,
CASE WHEN type='add' THEN grade_num ELSE grade_num-1 END g
FROM grade_info) a)b)c
ON u.id=c.user_id
WHERE r=1
ORDER BY id ASC
查看1道真题和解析