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

跟上一题差不多，多加了个case when
#窗口函数
select t.user_id,u.name, grade
from (select user_id,dense_rank() over(order by grade desc) rank1,grade from (select distinct user_id,sum(case when type='add' then grade_num
when type='reduce' then -grade_num end) over(partition by user_id) grade from grade_info ) a) t,user u
where t.user_id=u.id and rank1=1
order by t.user_id
#建多个子查询表
select a.user_id,u.name,a.grade_num from 
(select user_id,sum(case when type='add' then grade_num
when type='reduce' then -grade_num end) grade_num from grade_info 
group by user_id) a,user u
where a.user_id=u.id and
a.grade_num=(select max(grade_num) from (select user_id,sum(case when type='add' then grade_num when type='reduce' then -grade_num end) grade_num
from grade_info 
group by user_id)t)
order by a.user_id