题解 | #获得积分最多的人(三)#临时表

with grade as 
(
  select user_id,sum(grade_change) grade_num
  from (
    select user_id,grade_num
    ,case when type='add' then grade_num else (-1)*grade_num end grade_change
    from grade_info) as t1
  group by user_id
) -- 用户积分表

select id,name,grade_num
from (
  select user.id,name,grade_num
  ,dense_rank()over(order by grade_num desc) posn
  from user
  inner join grade
  on user.id=grade.user_id
) as t2
where posn=1