SQL 91) 获得积分最多的人（三）

在前面几道题的基础上用数据转置case when解决问题

select u.id, u.name , a.grade
from
(select user_id , g.grade , rank()over(order by g.grade desc) r_number
from
(select user_id ,
sum(case type when 'add' then grade_num
   else grade_num*(-1) end) grade
from grade_info
 group by user_id
 )g
group by user_id)a
join
user as u
on a.user_id = u.id
where a.r_number = 1
order by u.id asc;