本题,看似很简单,但是连表太难了,首先,根据 用户 id 分组查出 grade 的总值,然后,通过 总值,查询出 排名,然后输出排名是 1 的人即可,很多嵌套,所以不好理解,代码也不好读,建议花时间在这题上给他搞懂。
select id, name, grade_sum
from (
select user_id, grade_sum, dense_rank()over(order by grade_sum desc) rank_score
from (select user_id, sum(grade_num)over(partition by user_id) as grade_sum from grade_info) as b
group by user_id
) as a join user on a.user_id = user.id
where rank_score = 1
order by id