首先,将用户表与得分表连接,得到用户姓名得分总表,求和与按姓名分配 接着,运用窗口函数,找到排名第一的成绩,注意这里要求是要考虑并列情况,使用的为dense_rank()函数 最后,筛选出所需条件
select b.user_id,b.name,b.sum_g
from
( select a.user_id,a.name, a.sum_g
,dense_rank()over(order by a.sum_g desc ) as rank_n
from
( select user_id,name,sum(grade_num)as sum_g
from grade_info g
left join user u
on g.user_id = u.id
group by user_id
)a
group by 1
)b
where b.rank_n =1