题解 | #获得积分最多的人(二)#
获得积分最多的人(二)
http://www.nowcoder.com/practice/b6248d075d2d4213948b2e768080dc92
#第一步：提取、汇总数据
#第二步：dense_rank排名，第一名无论有几个人都是第一
#第三步：筛选即可
SELECT DISTINCT user_id, SUM(grade_num) over(PARTITION BY user_id) "grade_sum" FROM grade_info WHERE TYPE = "add"
#第二步：dense_rank排名，第一名无论有几个人都是第一
SELECT u.id, u.name, grade_sum, dense_rank() over(ORDER BY grade_sum DESC) "t_rank" FROM (SELECT DISTINCT user_id, SUM(grade_num) over(PARTITION BY user_id) "grade_sum" FROM grade_info WHERE TYPE = "add") t LEFT JOIN USER u ON t.user_id = u.id
#第三步：筛选即可
SELECT id, NAME, grade_sum FROM (SELECT u.id, u.name, grade_sum, dense_rank() over(ORDER BY grade_sum DESC) "t_rank" FROM (SELECT DISTINCT user_id, SUM(grade_num) over(PARTITION BY user_id) "grade_sum" FROM grade_info WHERE TYPE = "add") t LEFT JOIN USER u ON t.user_id = u.id) tt WHERE t_rank = 1
