解法一
1、 按user_id 分组聚合每个用户的总积分,并按总积分倒序排序,取排在第一位的用户信息
select user_id ,sum(grade_num) as grade_sum from grade_info group by user_id order by grade_sum desc limit 12、1表与user表连接,查询用户姓名和总积分
select b.name,a.grade_sum from (select user_id ,sum(grade_num) as grade_sum from grade_info group by user_id order by grade_sum desc limit 1) as a join user as b on a.user_id=b.id
解法二 窗口函数
user 表与grade_info 表连接,后使用窗口函数sum() 求出每个用户的总积分,然后按总积分倒序排序,取出排名第一的用户姓名和积分
select a.name,sum(b.grade_num) over(partition by user_id) as grade_sum from user as a join grade_info as b on a.id=b.user_id order by grade_sum desc limit 1;