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

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

子查询最内层

select

user_id,

sum(case when type="add" then grade_num else -1*grade_num end) as grade_num //如果是add 就返回grade_num 不是就返回*1grade_num

from grade_info

group by user_id

子查询中层

select

t2.user_id,

t2.grade_num,

rank() over (order by t2.grade_num desc) as t1 ） 按照积分降序排序 不连续 相同的积分序号相同

最外层

两个表进行连接

再筛选排名都是1的积分