解法一 子查询
1、查询最高的总积分
select max(grade_sum) from (select user_id,sum(grade_num) as grade_sum from grade_info group by user_id)2、user表与grade_info 连接,按id和name分组,统计总积分,并筛选出总积分=1表最高总积分的用户信息
select a.id,a.name,sum(b.grade_num) as grade_sum from user as a join grade_info as b on a.id=b.user_id group by a.id,a.name having grade_sum= (select max(grade_sum) from (select user_id,sum(grade_num) as grade_sum from grade_info group by user_id) as c) order by id;
解法二 分组聚合sum() 和 窗口函数rank()排名
1、按user_id分组求每个用户总积分,并增加排名列(按照总积分降序排名)
select user_id,sum(grade_num) as grade_sum, rank() over(order by sum(grade_num) desc ) as rnk from grade_info group by user_id2、1表与user表连接,查询排名第一的用户信息
select b.id,b.name,a.grade_sum from (select user_id,sum(grade_num) as grade_sum, rank() over(order by sum(grade_num) desc ) as rnk from grade_info group by user_id) as a join user as b on a.user_id=b.id where a.rnk=1 order by b.id;
解法三 with as
1、创建临时表,包含用户id ,name,总积分
with tmp_table as (select b.id,b.name,sum(a.grade_num) as grade_sum from grade_info as a join user as b on a.user_id=b.id group by b.id,b.name)2、使用1表查询总积分=最高积分的用户信息
with tmp_table as (select b.id,b.name,sum(a.grade_num) as grade_sum from grade_info as a join user as b on a.user_id=b.id group by b.id,b.name) select id,name,grade_sum from tmp_table where grade_sum = (select max(grade_sum) from tmp_table) order by id;