题解 | #网易云音乐推荐(网易校招笔试真题)#
获得积分最多的人(三)
http://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
select user_id id,name,grade_sum
from (
select user_id,name,grade_sum,rank()over(order by grade_sum desc) rk
from
(select user_id,name,
sum(case type when 'add' then grade_num else -grade_num end) grade_sum
from grade_info gi,user u
WHERE gi.user_id=u.id
group by user_id,name
) hh
) ww
where ww.rk=1
order by user_id
from (
select user_id,name,grade_sum,rank()over(order by grade_sum desc) rk
from
(select user_id,name,
sum(case type when 'add' then grade_num else -grade_num end) grade_sum
from grade_info gi,user u
WHERE gi.user_id=u.id
group by user_id,name
) hh
) ww
where ww.rk=1
order by user_id
