题解 | #获得积分最多的人(二)#
获得积分最多的人(二)
http://www.nowcoder.com/practice/b6248d075d2d4213948b2e768080dc92
SELECT id,name ,t
FROM user u
RIGHT JOIN
(SELECT user_id,SUM(grade_num) OVER (PARTITION BY user_id) t
FROM grade_info
ORDER BY t DESC
) a
ON u.id = a.user_id
GROUP BY id
HAVING t=(SELECT SUM(grade_num) c FROM grade_info GROUP BY user_id ORDER BY c DESC LIMIT 1)
ORDER BY t DESC
;
SELECT id,NAME,t
FROM
user u
RIGHT JOIN
(
SELECT DISTINCT(user_id),t,RANK() OVER (ORDER BY t DESC) r
FROM
(SELECT user_id, SUM(grade_num) OVER (PARTITION BY user_id) t
FROM grade_info) a) b
ON u.id=b.user_id
WHERE r=1
