SQL91 获得积分最多的人(三)

用case when讲grade改成带正负的 2. groupby id + sum() 3. dense_rank = 1

select id, name, grade_sum from
 (SELECT t2.user_id as id, t3.name as name, t2.sum2 as grade_sum, 
  dense_rank() over (order by t2.sum2 desc) as dr from
     (select user_id, sum(grade_num) as sum2 from 
         (select user_id, type, case
         when type = 'add' then grade_num
         when type = 'reduce' then -grade_num
         end as grade_num 
         from grade_info)t1
     group by user_id) as t2
 left join user as t3 on 
 t2.user_id = t3.id)t4
where dr = 1
order by id asc;