第一步通过group by和 sum对user_id的积分进行分组求和,即
(select user_id,
sum(grade_num) as t_num
from
grade_info
group by user_id) as a
第二步通过row_number窗口函数为第一步的积分进行排序,并select user_id,t_num形成b表备用,即
(select user_id,t_num,
row_number() over (order by t_num desc) as r
from
(第一步) as b
第三步通过join连接b表和user表,并通过where函数过滤掉排名不是第一的其他记录,即
select name,b.t_num
from
(select user_id,t_num,
row_number() over (order by t_num desc) as r
from
(select user_id,
sum(grade_num) as t_num
from
grade_info
group by user_id) as a) as b
join user as u on u.id=b.user_id
where b.r=1