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

select e.user_id,
e.name,
e.grade_sum
from
(select ,
dense_rank() over(order by d.grade_sum desc) as t_rank
from
(select distinct c.user_id,
c.name,
sum(c.grade_num_new) over(partition by c.user_id) as grade_sum
from
(select b.user_id,
a.name,
case when b.type='add' then b.grade_num else b.grade_num(-1) end as grade_num_new
from grade_info b
join
user a
on a.id = b.user_id) as c) as d) as e
where e.t_rank = 1
order by e.user_id;

比较复杂的方法，首先将name表（a表）和grade_info表（b表）连接，按照"add"和"reduce"分类用case函数进行正负赋值生成新的表c，从表c里对grade_num按照user_id进行分组求和，得到表d，从表d里按照求和逆序利用dense_rank窗口函数进行排序得到表e，最后提取表e中排序为1的记录既可