题解 | #获得积分最多的人(三)#
获得积分最多的人(三)
https://www.nowcoder.com/practice/d2b7e2a305a7499fb310dc82a43820e8
跟上一题差不多,多加了个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