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

select u.id,u.name,g.s from user u left join (select user_id, sum(grade_num) s from grade_info group by user_id) g on u.id = g.user_id
where g.s =
(select sum(grade_num) over(partition by user_id order by user_id) s_n 
from grade_info order by s_n desc limit 1)

思路：先求出最大值

select sum(grade_num) over(partition by user_id order by user_id) s_n 
from grade_info order by s_n desc limit 1

将两个表合并，求出各个id的sum值，通过where找出最大值

select u.id,u.name,g.s from user u left join (select user_id, sum(grade_num) s from grade_info group by user_id) g on u.id = g.user_id
where g.s =
(select sum(grade_num) over(partition by user_id order by user_id) s_n 
from grade_info order by s_n desc limit 1)